CODEWITHSUNDEEP
mysqlsql
Chris
Chris

Reputation: 14218

sql get total and filtered count in one query

I want to be able to tell what percentage of users for each team have more than 10 points. This currently requires two queries:

SELECT COUNT(*) as winners, team FROM users WHERE points > 10 GROUP BY team

SELECT COUNT(*) as total, team FROM users GROUP BY team

Can I do this in one so I get a result like this:

winners, total, team
5, 16, A

Upvotes: 3

Views: 4846

Answers (3)

arahman
arahman

Reputation: 585

Try this, it just uses a case when instead of filtering the entire dataset in the where clause.

SELECT COUNT(*) as total    
, SUM(case when points > 10 then 1 else 0 end) as winners
, team 
FROM users 
GROUP BY team

Can also do:

SELECT COUNT(*) as total    
, COUNT(case when points > 10 then 1 else NULL end) as winners
, team 
FROM users 
GROUP BY team

Upvotes: 2

Madhur Bhaiya
Madhur Bhaiya

Reputation: 28834

You can use Case .. When to check if points are more than 10 for a particular row, and count it accordingly (using Sum()).

SELECT COUNT(*) as total, 
       SUM(CASE WHEN points > 10 THEN 1 ELSE 0 END) AS winners, 
       team 
FROM users 
GROUP BY team

In MySQL, we can shorten it further as Sum() function can simply cast results of conditional operators/functions to 0/1 (for false/true respectively):

SELECT COUNT(*) as total, 
       SUM(points > 10) AS winners, 
       team 
FROM users 
GROUP BY team

Upvotes: 7

Zaynul Abadin Tuhin
Zaynul Abadin Tuhin

Reputation: 31993

you can try below way

SELECT COUNT(*) as winners, 
team, (select count(*) from users) as total FROM users 
WHERE points > 10 GROUP BY team

Or you can use case when

SELECT team, COUNT(*) as total , 
       count(CASE WHEN points > 10 THEN 1 end) AS winners  

FROM users 
GROUP BY team

Upvotes: 1

Related Questions