CODEWITHSUNDEEP
mysqlsqlgroup-by
XTRUST.ORG
XTRUST.ORG

Reputation: 3402

Group states and count number of zip codes in each state

I have a table with ZIP codes and country state. For example:

zip state d1 d2
99501 AK 0.00 0.00 
99502 AK 0.00 0.00
99503 AK 0.00 0.00
36513 AL 0.04 0.04
36518 AL 0.04 0.04

I'm trying to group states and count number of ZIP in each state:

SELECT 
    *
FROM 
  `#taxes`
GROUP BY
   `state`

How can I do this?

Upvotes: 1

Views: 368

Answers (2)

Gordon Linoff
Gordon Linoff

Reputation: 1270713

You use COUNT():

SELECT t.state, COUNT(*)
FROM `#taxes` t
GROUP BY t.state;

If a zip code could appear more than once for a given state, then you can use COUNT(DISTINCT zip) instead of COUNT(*).

Upvotes: 2

trincot
trincot

Reputation: 350866

Like this:

SELECT 
    state, count(distinct zip)
FROM 
  `#taxes`
GROUP BY
   state

Upvotes: 1

Related Questions