CODEWITHSUNDEEP
sqlt-sql
NealR
NealR

Reputation: 10709

Return row counts for field that appears more than x times

In my SQL table, I'd like to find the state codes that appear more than 300 times and also what the total row counts are for records containing each respective state code. More than a bit rusty on my SQL (plus it's 8am on a Monday...) and this is what i have so far: 

SELECT StateCode
FROM ZipCodeTerritory
HAVING COUNT(StateCode) > 300

Upvotes: 1

Views: 80

Answers (3)

StevieG
StevieG

Reputation: 8709

SELECT StateCode, count(*)
FROM ZipCodeTerritory
GROUP BY StateCode
HAVING count(*) > 300

Upvotes: 0

VancleiP
VancleiP

Reputation: 657

Something like this?

SELECT StateCode, count(StateCode)
FROM ZipCodeTerritory
GROUP BY StateCode
HAVING COUNT(StateCode) > 300

Upvotes: 1

Anup Agrawal
Anup Agrawal

Reputation: 6669

SELECT StateCode, Count(*) As StateCodeCount
FROM ZipCodeTerritory
Group By StateCode
HAVING COUNT(*) > 300

Upvotes: 2

Related Questions