Mahmoud ElMansy: knowledge meant to be free

25 Apr 2012

by ElMansy

2 Comments »

SQL Functions, SQL Query, SQL Scripts, SQL Server, tips & tricks

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;

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

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