Get several different counts in SQL query

Question

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?

Gordon Linoff · Accepted Answer · 2015-04-07 21:15:12Z

1

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

answered Apr 7, 2015 at 21:15

Gordon Linoff

1.3m62 gold badges706 silver badges857 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Alex · Accepted Answer · 2015-04-07 21:14:22Z

0

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

answered Apr 7, 2015 at 21:14

Alex

21.9k11 gold badges68 silver badges77 bronze badges

Collectives™ on Stack Overflow

Get several different counts in SQL query

2 Answers 2

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related