user2615302 user2615302 - 3 months ago 7
SQL Question

Multiple counts on the same SQL Server 2012 table

Is there a better way to count row of an id based on different criteria?

For instance I have a table that with columns:

id, state, city

My query:

ID, city, state, UScount, statecount, citycount
(select count(*) USCount, ID
from table
group by ID) UScount
inner join
(select count(*) stateCount, ID
from table
group by ID, state) statecount on UScount.ID = Statecount.ID
inner join
(select count(*) cityCount, ID
from table
group by ID, city) citycount on UScount.ID = citycount.ID

I am just trying to clean this up because in reality I have about 20 different counts and it does not look very nice


Are you looking for window functions?

select t.*,
       count(*) over () as cnt,
       count(*) over (partition by state) as cnt_state,
       count(*) over (partition by state, city) as cnt_state_city
from table t;

I am not sure if id should be part of the partition by or not.