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
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
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!
I’m usually to blogging and i actually admire your content. The article has really peaks my interest. I’m going to bookmark your site and maintain checking for brand new information.