How to make count in join I have three tables.


user
user_preferences
user_subscription_plan


User could have no user_subscription_plans

I would like to make a query that just gives a count with users that do not have a single entry in the user_subscription_plan

I have come up with this query:

SELECT
    user.id,
    COUNT( DISTINCT usp.user_id) AS user_sub_plans
FROM user
RIGHT JOIN user_preferences AS up ON up.user_id = user.id
LEFT JOIN user_subscription_plan AS usp ON usp.user_id = user.id
AND user.inactive = 0
GROUP BY 1


It returns the result like this:
**Result of the query **

id   user_sub_plan
---  -----------
100  0
101  1
102  1
103  0
104  1
105  0


Which means user with the id of 100 has no entries, and user 101 has entries.

If we observe the result set of the query, it shows there are three users that have entries. 
How could I expand this query, to return me user count of three?
Something like this:

user_count
    ---
3
The simplest way would be to use a subquery:

SELECT count(*) AS user_count FROM
(
    -- Original query, untouched
    SELECT
        user.id,
        COUNT( DISTINCT usp.user_id) AS user_sub_plans
    FROM user
    RIGHT JOIN user_preferences AS up ON up.user_id = user.id
    LEFT JOIN user_subscription_plan AS usp ON usp.user_id = user.id
    AND user.inactive = 0
    GROUP BY 1
    -- end of original query
) AS original_query
WHERE user_sub_plans &gt;= 1;


If you use recent version of MySQL, you can also use the WITH construct if it looks simpler to you:

WITH original_query AS ( -- original query ...)
SELECT count(*) AS user_count
FROM original_query
WHERE user_sub_plans &gt;= 1;


Given you want to do a nested count, I don't see an obvious way to do it without a subquery/WITH (nested querying). However, if you would only want to filter our some columns, without counting again, you can use HAVING, which is basically a post-GROUP BY, late executing WHERE:

SELECT
    user.id,
    COUNT( DISTINCT usp.user_id) AS user_sub_plans
    ...
    GROUP BY ...
    HAVING user_sub_plans &gt;= 1;


Outside of your query, there are a couple of not-wrong-but-weird decisions you took, like the RIGHT JOIN, and the AND user.inactive = 0 as part of the ON clause. I am not saying those are bad, but pointing you to double check those are things you want to do, and not unintentional bugs.