Skip to content
Advertisement

SQL Grand total without subtotals

I’m making a large SQL report in Orderwise, very roughly simplified as follows;

SELECT Supplier.SupplierName, POHeader.PODate, POHeader.PORef, POLine.LineID, SUM(Subquery.Val)
FROM Supplier INNER JOIN POHeader ON POheader.supplier = Supplier.SupplierID
    INNER JOIN POLine ON POLine.HeaderID = POHeader.PO_ID
    INNER JOIN Subquery on Subquery.POLine = POLine.Line_ID
GROUP BY Supplier.SupplierName, POHeader.PODate, POHeader.PORef, POLine.Line_ID

I want a grand total at the bottom, without a bunch of subtotals dotted in throughout the report – therefore I don’t think I can use ROLLUP. The Subquery in there is of course a sub query and in the real thing there will be twelve of them and all pretty complex, so I want to avoid a UNION just to total everything up if at all possible. Is there any other way I can put a Grand total row at the bottom of the report without subtotals?

Not completely sure of the SQL version but if it helps, google tells me Microsoft SQL Server Express or SQL Server Standard can be used with OrderWise

Advertisement

Answer

Use GROUPING SETS:

SELECT Supplier.SupplierName, POHeader.PODate, POHeader.PORef, POLine.LineID, SUM(Subquery.Val)
FROM Supplier INNER JOIN
     POHeader
     ON POheader.supplier = Supplier.SupplierID JOIN
     POLine
     ON POLine.HeaderID = POHeader.PO_ID JOIN
     Subquery 
     ON Subquery.POLine = POLine.Line_ID
GROUP BY GROUPING SETS ( (Supplier.SupplierName, POHeader.PODate, POHeader.PORef, POLine.Line_ID), () )
2 People found this is helpful
Advertisement