CODEWITHSUNDEEP
sqlsql-server
Jamaal
Jamaal

Reputation: 1489

How do I do a nested group by query in SQL Server?

I have the following query that I'm working on that involves producing a query involving a nested result of records but I'm not sure how to do a sub-query group by or if it's even possible at all.

create table places (
id int not null identity primary key,
country varchar(32) not null,
continent varchar(32) not null,
hits int not null
);

insert into places (country, continent, hits) values 
('Canada', 'North America', 8),
('Canada', 'North America', 5),
('United States', 'North America', 2),
('Germany', 'Europe', 5),
('Germany', 'Europe', 9),
('Germany', 'Europe', 1),
('France', 'Europe', 3),
('Italy', 'Europe', 6),
('Italy', 'Europe', 9),
('China', 'Asia', 7),
('China', 'Asia', 8),
('Japan', 'Asia', 7);

select country, count(*) as Total from places
group by country with rollup
order by country asc;

This query produces the following result:

(null)                12
Canada                2
China                 2
France                1
Germany               3
Italy                 2
Japan                 1
United States         1

I would like the query to produce a result that looks like this:

(null)                12
Asia                  3
  China               2
  Japan               1
Europe                6
  France              1
  Germany             3
  Italy               2
North America         3
  Canada              2
  United States       1

Here is a SQL Fiddle link to play with this: http://sqlfiddle.com/#!18/9145b/2

Hopefully this is possible to do in SQL Server and any help would be appreciated!

Upvotes: 1

Views: 86

Answers (2)

Serg
Serg

Reputation: 22811

Use grouping sets

select coalesce ('  ' + country, continent, 'TOTAL') item,  count(*) as Total 
from places
group by GROUPING sets((country, continent), (continent), ()) 
order by coalesce(continent, 'Z'), country asc;

Upvotes: 0

Gordon Linoff
Gordon Linoff

Reputation: 1269703

You can use grouping sets for the aggregation. Getting the right order is a bit tricky:

select coalesce(country, continent), count(*) as Total
from places
group by grouping sets ( (country), (continent), () )
order by (grouping(country) + grouping(continent)) desc, min(continent), grouping(continent);

Here is a db<>fiddle.

Upvotes: 4

Related Questions