SQL Server – Compute the Total of SUM(Column)

sql serversum

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.

Best Answer

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.

Related Question