CODEWITHSUNDEEP
mysqlsql
Tukinho
Tukinho

Reputation: 33

How to do multiple COUNT in one MySQL request?

I have a problem with MySQL.

I have these requests :

SELECT user_id, COUNT(*) 
FROM users_vuln 
WHERE vuln_id IN (48, 49, 50, 51, 52, 53, 54, 55, 56, 57, 58, 59, 60, 61, 62) 
GROUP BY user_id;

and

SELECT user_id, COUNT(*) 
FROM users_vuln 
WHERE vuln_id IN (48, 49, 50, 51, 52, 53, 54, 55, 56, 57, 58, 59, 60, 61, 62) 
AND concerned = 1 
GROUP BY user_id;

That works well alone, but I want to regroup these 2 requests in only 1 by having 2 columns for each COUNT.

like this :

user_id COUNT1 COUNT2
1 13 5
2 10 4
3 9 2

because for now, i only have this :

user_id COUNT1
1 13
2 10
3 9

and

user_id COUNT2
1 5
2 4
3 2

THANKS FOR YOU FUTURE ANSWERS ! <3

Upvotes: 1

Views: 41

Answers (1)

Tim Biegeleisen
Tim Biegeleisen

Reputation: 521804

Use conditional aggregation:

SELECT
    user_id,
    COUNT(*) AS COUNT1,
    SUM(concerned = 1) AS COUNT2
FROM users_vuln
WHERE vuln_id IN (48, 49, 50, 51, 52, 53, 54, 55, 56, 57, 58, 59, 60, 61, 62)
GROUP BY user_id;

Upvotes: 1

Related Questions