THE DOCTOR THE DOCTOR - 7 months ago 18
SQL Question

MySQL Query Frequency of Zip Codes per Offices

I am trying to write a MySQL query to see which zip codes come up the most frequently for our customers and need help in completing it. The office field at the end of my select statement can be anyway from 1 to 10 possibilities per zip code.

How would I fix this query to get me the most frequent zip codes and which out of the 10 possible offices happen to have a location in that zip code?

SELECT SUBSTRING(zip_code, 1, 5) AS zip,
COUNT(*) AS freq, office
FROM customer_billing
WHERE status != 'deleted'
GROUP BY zip
ORDER BY freq DESC


Sample output for what I am looking for:

zip freq office
---- ---- ----
92101 450 office_A, office_B
02124 300 office_A, office_C, office_D
07713 110 office_B

Answer

Use GROUP_CONCAT along with DISTINCT. Using DISTINCT is necessary because a given office may occur multiple times for a given zip code.

SELECT SUBSTRING(zip_code, 1, 5), COUNT(*) AS freq,
    GROUP_CONCAT(DISTINCT office ORDER BY office DESC SEPARATOR ',')
FROM customer_billing
WHERE status != 'deleted'
GROUP BY SUBSTRING(zip_code, 1, 5)
ORDER BY freq DESC