asked on

Group By and totals of count(*)

I have a table with land owner info among other things. I want to show how many listings I have for each county within a state and then a total for each state. Currently I can get a total for each county but not the state by doing the following:

select state, cnty, count(*)
from plat
where cnty in (228,255,256,199,399)
group by state, cnty

This is giving me the total listings for each county (cnty) but how do I get a total for all the listings in the state? In the example cnty 228, 255, 256 are all county numbers in Wisonsin.

SOLUTION

sventhan

membership

Create a free account to see this answer

Signing up is free and takes 30 seconds. No credit card required.

See answer

ASKER CERTIFIED SOLUTION

cyberkiwi

membership

Create a free account to see this answer

Signing up is free and takes 30 seconds. No credit card required.

See answer

sventhan

with some test data...

with cte as
(select 'MA' state, 223 cnty, 10 nos union
select 'MA' state, 223 cnty, 10 union
select 'MA' state, 256 cnty, 10 union
select 'MA' state, 199 cnty, 10 union
select 'MA' state, 399 cnty, 10 union
select 'MA' state, 256 cnty, 10 )

select state, cnty
,count(*) over (partition by state,cnty) by_cnty
,count(*) over (partition by state ) by_state
from cte
where cnty in (228,255,256,199,399)

SOLUTION

Sharath S

membership

Create a free account to see this answer

Signing up is free and takes 30 seconds. No credit card required.

See answer

SOLUTION

cyberkiwi

membership

Create a free account to see this answer

Signing up is free and takes 30 seconds. No credit card required.

See answer