SQL SERVER – Using Group by With Rollup (Transact-SQL)
Generates the simple GROUP BY aggregate rows, plus subtotal or super-aggregate rows, and also a grand total row.
The number of groupings that is returned equals the number of expressions in the plus one. For example, Consider the Following statement.
SELECT a, b, c, SUM (EXPRESSION)
FROM T
GROUP BY ROLLUP;
FROM T
GROUP BY ROLLUP;
Here is an example.
USE AdventureWorks
SELECT SalesOrderID,sod.ProductID ,SUM(LineTotal) AS SubTotal
FROM Sales.SalesOrderDetail AS sod
GROUP BY SalesOrderID ,ProductID
WITH rollup
SELECT SalesOrderID,sod.ProductID ,SUM(LineTotal) AS SubTotal
FROM Sales.SalesOrderDetail AS sod
GROUP BY SalesOrderID ,ProductID
WITH rollup
We will find totals in the ending of each group
and if we want to found only totals .
WITH a AS(
SELECT SalesOrderID,sod.ProductID ,SUM(LineTotal) AS SubTotal
FROM Sales.SalesOrderDetail AS sod
GROUP BY SalesOrderID ,ProductID
WITH rollup
) SELECT * FROM a WHERE ProductID IS NULL
SELECT SalesOrderID,sod.ProductID ,SUM(LineTotal) AS SubTotal
FROM Sales.SalesOrderDetail AS sod
GROUP BY SalesOrderID ,ProductID
WITH rollup
) SELECT * FROM a WHERE ProductID IS NULL
My name is Mahmoud El Mansy I'm C# Senior solution developer .
You are so cool! I don’t believe I have read through a single thing like this before. So wonderful to find another person with some original thoughts on this topic. Seriously.. many thanks for starting this up. This web site is one thing that is required on the web, someone with a little originality!