I have a database full of zip codes, like this:

95945
95945
48864
48864
95630
95959
etc.

I'd like to find out how many of each zip I have. Is there query that I can do that will tell me this? It would be great if there is a query that will tell me: 959459 (2), 48864 (2), 95630 (1), 95959 (1), or something like that. I don't know all of the zip codes in the database so I can't query one-by-one.

Any suggestions?


Thanks,
WebGrrl

You can find out each separate ZIP code (i.e. no repetitions) by using:

SELECT DISTINCT ZipCode FROM MyTable

You could then iterate through them using the SQL COUNT statement. That's a bit long winded though, I'm sure there's another way to do this. Try:

SELECT COUNT(ZipCode), ZipCode
FROM MyTable
GROUP BY ZipCode

If that doesn't work (it's off the top of my head at 1am), look up on Google the syntax of COUNT and DISTINCT in SQL and you should be able to build something...

Try this:

SELECT ZipCode, COUNT(ZipCode) as Total
FROM MyTable
GROUP BY ZipCode
ORDER BY Total DESC

Cool - I will try this!

Thanks,
WebGrrl