user2615302 user2615302 - 5 months ago 14
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:

select
ID, city, state, UScount, statecount, citycount
from
(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

Answer

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.