Double "group by" query needed

[marcasii]

I have the following data:

Country Option
-----------------
US a
Australia a
Africa b
Africa c
US b
Australia a
US c
Australia b
Australia b

and I need to generate a query that reports the following information

Australia 4 (total entries)
a 2 (total number od a's in Australia)
b 2
US 3
a 1
b 1
c 1
Africa 2
b 1
c 1

Any iadeas on the query?

 

[Twofy]

Damn!
Is that your two only column? It's not really relational, if it is. Have you something that stand for primary key?

 

[marcasii]

Yes, well no. There is of course an "ID" column which is auto incrementing. I have just simplified it to the relevant data.

 

[vongrunt]

It is possible but not practical at all:

select case when [option]='*' then country else ' ' + [option] end, cnt
from 
(	select * from 
	(	select country, [option], count(*) as cnt
		from blah 
		group by country, [option]
	) X
	union
		select country, '*', count(*)
		from blah
		group by country
) Y

Better use standard GROUP BY stuff:

select country, [option], count(*) as cnt
from blah 
group by country, [option]
order by country, [option]

... then calculate/display subtotals client-side.