2

I am interested in the totals and the details of every line, in other words, the results of the second query.

Is there an easier way to do a rollup, and just have grandtotals? (my 'live' query has more group levels that his example...)

CREATE TABLE #TEMP (a int, b int, c int)

INSERT INTO #TEMP VALUES( 1,1,1)
INSERT INTO #TEMP VALUES( 1,1,2)
INSERT INTO #TEMP VALUES( 1,1,3)
INSERT INTO #TEMP VALUES( 1,2,1)
INSERT INTO #TEMP VALUES( 1,2,2)
INSERT INTO #TEMP VALUES( 1,2,3)
INSERT INTO #TEMP VALUES( 1,3,1)
INSERT INTO #TEMP VALUES( 1,3,2)
INSERT INTO #TEMP VALUES( 1,3,3)

SELECT a,b,c, COUNT(*)
FROM #TEMP
GROUP BY ROLLUP(a,b,c)

-- 2nd query
SELECT a,b,c, COUNT(*)
FROM #TEMP
GROUP BY ROLLUP(a,b, c)
HAVING (c is not null and b is not null) or a is null
2
  • 1
    Can you change the subject so that it describes the functional problem you’re trying to solve? Oct 17, 2018 at 12:32
  • change title from "Can this be done simpler?" to "Change level(s) of totals with GROUP BY (in MS-SQL"
    – Luuk
    Oct 18, 2018 at 17:36

1 Answer 1

2

(You don't say which DBMS, but inferring SQL Server from your temporary table name..)

If you're on a recent enough version (>=2008 ?) you can achieve the fine level of rollup control you're after with GROUPING SETS. For your 2nd query, the equivalent would be:

SELECT a,b,c, COUNT(*)
FROM #TEMP
GROUP BY GROUPING SETS ((a,b,c),())
1
  • 1
    That did the trick!
    – Luuk
    Oct 18, 2018 at 17:40

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.