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
ID, city, state, UScount, statecount, citycount
(select count(*) USCount, ID
group by ID) UScount
(select count(*) stateCount, ID
group by ID, state) statecount on UScount.ID = Statecount.ID
(select count(*) cityCount, ID
group by ID, city) citycount on UScount.ID = citycount.ID
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.