How to combine CUBE and ROLLUP?

I have a query with an important GROUP BY aggregation. Here is a simplified schema (SQL Fiddle):

CREATE TABLE [data] (
    [year] [int] NOT NULL ,
    [month] [int] NOT NULL ,
    [country] [varchar] (32) NOT NULL ,
    [state] [varchar]   (32),
    [city] [varchar]  (32) NOT NULL ,
    [federation] [varchar]  (32) NOT NULL ,
    [id] [int] NOT NULL ,
    [price] [int] NOT NULL );

INSERT data values(2012, 12, 'France', NULL, 'Paris', 'FFJ', 1, 23)
INSERT data values(2013, 2, 'France', NULL, 'Paris', 'FFV', 2, 212)
INSERT data values(2012, 1, 'USA', 'CA', 'Paris', 'FFV', 3, 23)
INSERT data values(2013, 12, 'France', NULL, 'Paris', 'FFV', 4, 273)
INSERT data values(2012, 9, 'USA', 'OR', 'Lake Oswego', 'FFV', 5, 743)
INSERT data values(2012, 11, 'France', NULL, 'Paris', 'FFJ', 6, 3)
INSERT data values(2012, 12, 'France', NULL, 'Paris', 'FFV', 7, 231)
INSERT data values(2012, 12, 'USA', 'CA', 'St Monica', 'FFV', 8, 41)
INSERT data values(2012, 12, 'France', NULL, 'Paris', 'FFV', 9, 96)
INSERT data values(2012, 12, 'France', NULL, 'Vire', 'FFJ', 10, 23)
INSERT data values(2012, 12, 'France', NULL, 'Paris', 'FFV', 11, 58)
INSERT data values(2012, 12, 'France', NULL, 'Nice', 'FFV', 12, 773)

I group by columns year, month, country, state, federation, city.

I use the ROLLUP operator because I need to get totals on multiple levels. But I would also like to apply CUBE to two of the columns, country (a geographical attribute) and federation (and organizational attribute).

If I just CUBE in my query, I get much more output than I need. (I really don't need to apply CUBE to all my GROUP BY columns.)

Does a workaround exist?

Solution

SQL Server 2000 supports only the non-ISO-compliant GROUP BY ... WITH ROLLUP and GROUP BY ... WITH CUBE, two pieces of syntax that does not offer much flexibility. You can have only a full rollup/cube with those, no variations or combinations.

However, a workaround in your particular case might not be too cumbersome. The ROLLUP would give you the following groupings:

year, month, country, state, federation, city
year, month, country, state, federation
year, month, country, state
year, month, country
year, month
year
()  -- grand total

And my understanding of what you want instead is this:

year, month, country, state, federation, city
year, month, country, state, federation
year, month, country, state
year, month, country, federation
year, month, country
year, month
year
()

Apparently you need to add just one more grouping subset to the ROLLUP output, namely this one:

year, month, country, federation

You could just calculate it in a separate SELECT and combine the results with UNION ALL:

SELECT ...
FROM dbo.data
GROUP BY
  year, month, country, state, federation, city
  WITH ROLLUP

UNION ALL

SELECT ...
FROM dbo.data
GROUP BY
  year, month, country, federation
;