CODEWITHSUNDEEP
mysqlsqldatabase
Christoffer
Christoffer

Reputation: 7777

Get several different counts in SQL query

I'm doing this query

SELECT zip, COUNT(*) AS amount 
FROM table
WHERE group_id = 19
GROUP BY zip ORDER BY amount DESC

This gives me a list with the number of occurrences of group_id 19 per zip-code. But I also want a total count of all groups_id's for every zip (without the WHERE clause). Like this (dummy code)

SELECT zip, COUNT(*) AS amount, COUNT(*) FOR THIS ZIP AS total, (amount/total * 100) AS percent
FROM table
WHERE group_id = 19
GROUP BY zip ORDER BY amount DESC

Is this possible?

Upvotes: 1

Views: 33

Answers (2)

Gordon Linoff
Gordon Linoff

Reputation: 1269923

You can use conditional aggregation:

SELECT zip, COUNT(*) as amount, SUM(group_id = 19) AS grp19_amount,
       AVG(group_id = 19) as percent
FROM table
GROUP BY zip
ORDER BY grp19_amount DESC

Upvotes: 1

Alex
Alex

Reputation: 21766

In the SELECT query you can only include columns that are included in the GROUP BY clause or results from aggregating functions. So COUNT(*) should be fine, however, (amount/total * 100) will not work unless you include an add aggregating function such as SUM

Upvotes: 0

Related Questions