G’Day,
I was recently looking at getting some sales data from SQL Server, aggregating some totals and then displaying a grand total.
This is the sort of thing that very often gets done on the client side – but as it turns out, this can be accomplished using standard T-SQL, and, before you ask, no, there are no cursors in sight.
So I put together some sample data, here it is
USE tempdb;
GO
/*
Create a simple table.
We'll simply group by ClientName
*/
IF OBJECT_ID('dbo.Sales') IS NOT NULL
BEGIN
DROP TABLE dbo.Sales;
END;
GO
CREATE TABLE dbo.Sales
(
SaleID INT NOT NULL IDENTITY(1,1)
,ClientName NVARCHAR(10) NULL
,Amount INT NOT NULL
);
GO
/*
Insert some data
*/
INSERT INTO dbo.Sales(ClientName , Amount)
SELECT
ClientName
, Amount
FROM
(
VALUES
('Marty' , 10),
('Marty' , 1),
('Marty' , 3),
('Callum' , 6),
('Callum' , 3),
('Callum' , 23),
('Jake' , 45),
('Jake' , 3),
('Jake' , 67),
('Bert' , 5),
('Bert' , 1),
('Bert' , 0),
('Bert' , 6),
('Zeb' , 35),
('Zeb' , 23),
('Zeb' , 12)
) AS TempSales(ClientName,Amount);
-----------------------------------------------------------------
The output that we’d really like – using only T-SQL – is like below.
Note that the last line contains a grand total.
While, there are a few ways we might be able to get this result set, lets concentrate on one in particular, and that is using ROLLUP and GROUPING
The following query comes pretty close
SELECT
[Full Name] = ClientName
, [Units] = SUM(Amount)
, [Grouping] = GROUPING(ClientName)
FROM
dbo.Sales
GROUP BY ROLLUP(ClientName)
ORDER BY
[Grouping] ASC , [Full Name];
and gives us
Which is close, but note we have a column called “Grouping” – however the Total looks fine, except that it is registering as NULL!
So, how did the “Grouping” column get there?
Well, first off – I decided to call it “Grouping”, but I could have literally called it anything that I wanted.
The column actually came from he use of the T-SQL GROUPING function. This function is basically used for totaling groups
The totaled column (in this case there is only one) has a NULL value – and the 1 in the “Grouping” column indicates that this NULL column is giving us a grand total – which will differentiate it from any NULL values in the same column that are not grand totals.
To demonstrate, lets change the value ‘Bert’ in our original table to be NULL
UPDATE dbo.Sales SET ClientName = NULL WHERE ClientName = 'Bert'
and when we run the query again
SELECT
[Full Name] = ClientName
, [Units] = SUM(Amount)
, [Grouping] = GROUPING(ClientName)
FROM
dbo.Sales
GROUP BY ROLLUP(ClientName)
ORDER BY
[Grouping] ASC , [Full Name];
we get the result
Note that we now have two NULL values – one of which actually represents a Grand Total and the other just represents NULL values in the table.
Also note that SQL has grouped the NULL values from the table together – so watch out for how NULLs are handled in various T-SQL expressions.
So the only way we know which NULL represents a Grand Total is by looking at the “Grouping” column – the one that has a Grand Total value of 1, while the aggregated NULL values from the table are represented by a 0 in the “Grouping” column.
With this knowledge, lets try and tidy up this output to get the output that we originally wanted – I chose to use a CTE
WITH ClientAmountsCTE
AS
(
SELECT
[Client Name] = ClientName
, [Units] = SUM(Amount)
, [Total] = GROUPING(ClientName)
FROM
dbo.Sales
GROUP BY ROLLUP(ClientName)
)
SELECT
[Client Name] =
CASE
WHEN [Total] = 1 THEN 'Total'
WHEN [Client Name] IS NULL THEN 'UNKNOWN'
ELSE [Client Name]
END
, [Units]
--, [Total]
FROM
ClientAmountsCTE
ORDER BY
[Total] ASC;
GO
which gives us the result
Notice that a CASE statement has been used to differentiate the NULL columns based on the value of the “Grouping” column (which is itself not used in the display output).
ROLLUP can do a little more than this when multiple columns are involved, so check out the docs.
Note also, that there are two ways to use ROLLUP – one way is NON-ISO compliant (uses WITH) and the other way is ISO compliant (does not use WITH)
Examples
-----------------------------------------------------------------
/*
Select the data grou abd use the ROLLUP syntax
Non ISO compiant syntax (uses WITH)
*/
SELECT
[Full Name] = ClientName
, [Units] = SUM(Amount)
, [Total] = GROUPING(ClientName)
FROM
dbo.Sales
GROUP BY ClientName WITH ROLLUP
ORDER BY
[Full Name] DESC;
-----------------------------------------------------------------
/*
Select the data grou abd use the ROLLUP syntax
Fully ISO compliant syntax (does not use WITH)
*/
SELECT
[Full Name] = ClientName
, [Units] = SUM(Amount)
, [Total] = GROUPING(ClientName)
FROM
dbo.Sales
GROUP BY ROLLUP(ClientName)
ORDER BY
[Full Name] DESC;
Hope this helps somebody.
Have a great day.
Cheers
Marty
