1

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

3
  • "to return me user count of three" I didn't get that part, may I ask what you mean there? Are you asking to filter out users that have at least 1 in the value of user_sub_plan ?
    – jynus
    Jul 31, 2019 at 15:22
  • I have clarified the last part.
    – Amiga500
    Jul 31, 2019 at 15:24
  • 1
    Thanks, gotcha, will try to answer now.
    – jynus
    Jul 31, 2019 at 15:25

1 Answer 1

1

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 >= 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 >= 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 >= 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.

6
  • In your first example, the user_sub_plan is a problem. Error message is Every derived table must have its own alias
    – Amiga500
    Jul 31, 2019 at 15:40
  • 1
    easy fix :-) I am doing it by heart. Check now.
    – jynus
    Jul 31, 2019 at 15:41
  • Now I receive: Unknown column 'user_sub_plan' in 'where clause' :)
    – Amiga500
    Jul 31, 2019 at 15:43
  • Another typo. I didn't want me to do all the work! Fixed. :-D
    – jynus
    Jul 31, 2019 at 15:44
  • 1
    Please check the comments bellow, they are not part of the answer, but it may be helpful to you (highly dependent on what you want to do).
    – jynus
    Jul 31, 2019 at 15:46

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.