I created for a client a "Dashboard" that shows them how many active users they have with subscriptions.
I used Sourcerer and this code:
Code:
SELECT COUNT(DISTINCT(u.`id`))
FROM `#__cbsubs_subscriptions` AS sub, #__users AS u
WHERE sub.`user_id` = u.`id`
and sub.`plan_id` IN (1,2,3,4,5,6,7,8,9,10)
AND sub.`status` = 'A'
AND u.`block` = 0
which results in "258"
I then decided to breakdown their membership into their different plans like this using Plotalot using a line for each of their 10 Plans:
Code:
SELECT 'Standard',
COUNT(DISTINCT(u.id))
from j17_users as u, j17_cbsubs_subscriptions as sub
WHERE sub.plan_id = 1
and sub.id = u.id
AND u.block=0
and sub.status='A'
In total I got only 118 when added up.
I figured out that some people have multiple subscriptions even though I have the system set to only one at a time - some people have a single lifetime free subscription plus another Plan - not sure how but they do - maybe because one is free and the other is not (?)
Is there a better way to code these queries that will result in the actual number of users we have and not the number of subscriptions or is my second query the better way to do this? Maybe by associating the plan with the latest subscription date?
