ROLLUP allows to aggregate across multiple levels of grouping as if I UNIONed multiple simple SELECT statements.
But I want to be able to aggregate results of lower level of grouping as if I used nested SELECT statements or chain of CTEs dependent upon each other.
For example, I want to be able to count number of groups from lower level of grouping or calculate average of sums of lower level or minimum of maximums of lower level, e.t.c.
More specific example: if I have record for each auto accident in USA and I want to get not only count of accidents on each level in ROLLUP(state,county,city,zip), but also count of people (obviously each person may be involved in multiple accidents and consequently multiple records).
Is it possible to achieve that with ROLLUP ? If possible, then how ?
SQL example with results:
if object_id('accident') is not null drop table accident
create table accident(
id int identity(1,1)
,state varchar(50)
,city varchar(50)
,zip varchar(50)
,person varchar(50)
)
insert accident(state,city,zip,person)values
('NY','Manhattan',10001,'John')
,('NY','Manhattan',10001,'John')
,('NY','Manhattan',10001,'Barbara')
select
state,city,zip,person
,accidents=count(1)
-- the following line causes error: Windowed functions cannot be used in the context of another windowed function or aggregate.
--,people=sum(case when row_number()over(partition by person order by (select 0))=1 then 1 else 0 end)
from accident
group by rollup(state,city,zip,person)
;with person as (select state,city,zip,person from accident group by state,city,zip,person)
select
state,city,zip
,people=count(1)
from person
group by rollup(state,city,zip)
Results:
state city zip person accidents
NY Manhattan 10001 Barbara 1
NY Manhattan 10001 John 2
NY Manhattan 10001 NULL 3
NY Manhattan NULL NULL 3
NY NULL NULL NULL 3
NULL NULL NULL NULL 3
state city zip people
NY Manhattan 10001 2
NY Manhattan NULL 2
NY NULL NULL 2
NULL NULL NULL 2
See first result returns 3 accidents for each level and second returns 2. If want to get both 3 and 2 in one ROLLUP query. My problem is that windowed functions cannot be nested.
What I just asked can be achieved with this query:
;with person as (select state,city,zip,person,accidents=count(1) from accident group by state,city,zip,person)
select
state,city,zip
,accidents=sum(accidents)
,people=count(1)
from person
group by rollup(state,city,zip)
state city zip accidents people
NY Manhattan 10001 3 2
NY Manhattan NULL 3 2
NY NULL NULL 3 2
NULL NULL NULL 3 2
but that way of doing it requires writing CTE for each level explicitly.
I want to be able to write one query that has access to results of lower level of grouping regardless of number of grouping levels.
Tried this:
;with
lvl as (
select *
,lvl = -1
,accidents=1
,people=1
from accident
union all
select accident.*
,lvl = grouping_id(accident.state,accident.city,accident.zip,accident.person)
,accidents=sum(accidents)
,people=count(1)
from accident
join lvl prev on prev.lvl = (grouping_id(accident.state,accident.city,accident.zip,accident.person)+1)/2-1
group by rollup(accident.state,accident.city,accident.zip,accident.person)
)
select * from lvl
But got errors:
Msg 1015, Level 15, State 1, ...
An aggregate cannot appear in an ON clause unless it is in a subquery contained in a HAVING clause or select list, and the column being aggregated is an outer reference.
Msg 467, Level 16, State 1, ...
GROUP BY, HAVING, or aggregate functions are not allowed in the recursive part of a recursive common table expression 'lvl'.
Related question: recursive sql function with rollup logic?
