As we know GROUP BY clause used to groups the result set
into the summary. For example, please execute the following query.
I am creating a productSales table which has sales data
of product for the country.
CREATE TABLE
#ProductSales
(
ID INT IDENTITY(1,1),
Country VARCHAR(500),
Product VARCHAR(400),
Sales DECIMAL(16,2),
CreatedDate DATETIME
)
INSERT INTO
#ProductSales
SELECT 'INDIA','A',200000,'01/01/2015'
UNION ALL
SELECT 'INDIA','B',400000,'02/01/2015'
UNION ALL
SELECT 'INDIA','C',100000,'02/01/2015'
UNION ALL
SELECT 'USA','A',300000,'03/01/2014'
UNION ALL
SELECT 'USA','B',500000,'04/01/2014'
UNION ALL
SELECT 'CHINA','A',600000,'03/01/2014'
UNION ALL
SELECT 'CHINA','B',800000,'04/01/2014'
|
Grouping result set using GROUP BY clause in bellow
query.
SELECT Country, Product, SUM(Sales) AS Sales FROM #ProductSales
GROUP BY
Country, Product
|
Result-Set
Country
Product Sales
------------------------------------------------------
CHINA
A 600000.00
INDIA
A 200000.00
USA
A 300000.00
CHINA
B 800000.00
INDIA
B 400000.00
USA
B 500000.00
INDIA
C 100000.00
(7 row(s) affected)
WITH ROLLUP: It is used to generate a subtotal row
and grand total row for aggregate row. Please see the following script.
SELECT Country, Product, SUM(Sales) AS [Total Sales] FROM #ProductSales
GROUP BY
Country, Product WITH ROLLUP
|
Result-Set
Country
Product Total Sales
------------------------------------------
CHINA
A 600000.00
CHINA
B 800000.00
CHINA NULL 1400000.00-- Total Sales of China
INDIA
A 200000.00
INDIA
B 400000.00
INDIA
C 100000.00
INDIA NULL 700000.00-- Total Sales of India
USA
A 300000.00
USA
B 500000.00
USA NULL 800000.00-- Total Sales of USA
NULL NULL 2900000.00-- Grand Total sales
(11 row(s) affected)
No comments:
Post a Comment
Please do not enter any spam link in the comment box.