8

I have this code that sums up the qty for a certain item (itemid) and by its product date code (proddte).

select sum(qty), itemid, proddte 
from testtable where .... 
group by itemid, proddte

What I want to do is to get the total of all qty regardless of itemid/proddte. I have tried:

select sum(qty), itemid, proddte, sum(qty) over() as grandtotal 
from testtable 
where .... 
group by itemid, proddte

But it says I should also have qty in the group by clause. If I did that, the result will not be equal to my expected result.

It does not absolutely need to be represented as a separate column, with the same value in every row. Any representation is accepted as long as I can display the overall total.

9
CREATE TABLE #foo
(
 itemid int, 
 proddte date,
 qty int
);

INSERT #foo(itemid,proddte,qty) VALUES
(1,'20140101',5),(1,'20140102',7),(2,'20150101',10);


-- if it really needs to be a column with the same value
-- in every row, just calculate once and assign it to a variable

DECLARE @sum int = (SELECT SUM(qty) FROM #foo);

SELECT itemid, proddte, GroupedSum = SUM(qty), GrandTotal = @sum
  FROM #foo
  GROUP BY itemid, proddte;

-- if the grand total can be expressed on its own row, 
-- you can use GROUP BY GROUPING SETS:
SELECT itemid, proddte, SUM(qty)
  FROM #foo GROUP BY GROUPING SETS((),(itemid,proddte));

-- if that syntax is confusing, you can use a less
-- efficient UNION ALL:
SELECT itemid, proddte, SUM(qty)
  FROM #foo GROUP BY itemid,proddte
UNION ALL
SELECT NULL, NULL, SUM(qty) 
  FROM #foo;

GO
DROP TABLE #foo;

The GROUP BY GROUPING SETS is is basically a UNION ALL. The () means just take the SUM regardless of grouping, any other group listed gets aggregated separately. Try GROUP BY GROUPING SETS ((itemid),(itemid,proddte)) to see the difference.

For more details see the documentation:

Using GROUP BY with ROLLUP, CUBE, and GROUPING SETS

As Andriy mentioned, the query above could also be written using:

GROUP BY ROLLUP( (itemid,proddte) )

Note the two columns there are enclosed in an additional pair of parentheses, making them a single unit. Andriy wrote a demo hosted on the Stack Exchange Data Explorer.

  • 1
    @niq: GROUP BY ROLLUP((itemid,proddte)) would produce the same result and might be less confusing. – Andriy M Jun 7 '16 at 8:58
  • @AndriyM that isn't equivalent as it will include a sub total for itemid I.e. It is equivalent to GROUP BY GROUPING SETS((),(itemid),(itemid,proddte)) – Martin Smith Jun 7 '16 at 9:34
  • 4
    @MartinSmith: No, the columns are enclosed in an additional pair of brackets, which makes them a single unit. GROUP BY ROLLUP(itemid,proddte), on the other hand, would indeed produce (additional) subtotals on itemid (same as GROUP BY ROLLUP((itemid),(proddte))). Demo at SEDE – Andriy M Jun 7 '16 at 9:44
  • 3
    @AndriyM I stand corrected. Though that does undermine the "less confusing" point because it managed to confuse at least one person :-) – Martin Smith Jun 7 '16 at 9:49
  • 2
    @AndriyM I don't find GROUP BY ROLLUP less confusing, but it is rather subjective. I also always get nervous when I read things like The non-ISO compliant WITH ROLLUP, WITH CUBE, and ALL syntax is deprecated - why I tend to favor GROUPING SETS. – Aaron Bertrand Jun 7 '16 at 14:02
8

This is valid syntax, too:

       sum(sum(qty)) over ()

It's a bit confusing when one sees it at first but you only have to remember that the window functions - e.g. sum() over () - are applied after the group by so everything that can appear in the select list of a group by query can be placed inside a window aggregate. So (the qty cannot but) the sum(qty) can be placed inside sum() over ():

select sum(qty), itemid, proddte, 
       sum(sum(qty)) over () as grandtotal  
from testtable 
where .... 
group by itemid, proddte ;

Having said that, I'd prefer the GROUPING SETS query provided by Aaron Bertrand. The total sum needs to be shown once and not in every row.

Also note that while the sum of sums can be used to calculate the total sum, if you wanted the total count, you'd have to use the sum of counts (and not the count of counts!):

sum(count(*)) over ()  as grand_count

And if one wanted the average over all the table, it would be even more complicated:

sum(sum(qty)) over ()
/ sum(count(qty)) over ()  as grand_average

because the average of averages is not the same as the average over all. (If you try the avg(avg(qty)) over () you'll see that it may yield a different result than the above grand average.)

3

One possible way around is to wrap the first GROUP BY into CTE:

WITH
CTE
AS
(
    select
        itemid
        ,proddte
        ,sum(qty) AS SumQty
    from testtable 
    where .... 
    group by itemid, proddte
)
SELECT
    itemid
    ,proddte
    ,SumQty
    ,SUM(SumQty) OVER () AS grandtotal
FROM CTE
;
  • 3
    There's no need for the CTE as ypercube's answer illustrates – Martin Smith Jun 7 '16 at 9:35
  • 1
    @MartinSmith, you are right. Any non-recursive CTE can be re-written as a subquery of some form. SQL Server's optimizer inlines CTEs anyway (as opposed to Postgres, for example), so execution plan is the same with CTE or without. Quite often, though, it is easier to read and understand complex queries if they are broken down into simpler parts using CTE. At least for me. – Vladimir Baranov Jun 7 '16 at 11:22
  • 3
    I think you misunderstood Martin's point, although your point about CTEs adding readability may still stand. What ypercube's suggestion shows is that you can avoid a subquery of any form in this case, whether a CTE, a derived table or a calculated column as a scalar aggregation subquery. – Andriy M Jun 7 '16 at 11:29
  • 1
    @AndriyM, I like the variant from ypercube's answer and I didn't think of such syntax before I saw it here. It is always good to learn something new. You are right, my main point boils down to readability. In my tests optimizer generated the same execution plan, with CTE or without. – Vladimir Baranov Jun 7 '16 at 12:18

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.