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
WHERE status != 'deleted'
GROUP BY zip
ORDER BY freq DESC
zip freq office
---- ---- ----
92101 450 office_A, office_B
02124 300 office_A, office_C, office_D
07713 110 office_B
GROUP_CONCAT along with
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