Thursday, 9 February 2012

SQL Server GROUPING SETS

DECLARE @Products TABLE (
  Product varchar(50),
  Category varchar(50),
  Price float
)
INSERT INTO @Products
SELECT 'Sugar','Groceries',2.35
UNION SELECT 'Bread','Groceries',1.09
UNION SELECT 'Ecomonist','Magazines',4.99
UNION SELECT 'FT','Magazines',2.99
SELECT Product,Category, SUM(Price) AS Price, COUNT(*) AS [Count]
FROM @Products
GROUP BY GROUPING SETS((Product),(Category),())
ORDER BY Product DESC, Category DESC, Price DESC, [Count] DESC
SELECT Product,[Range], SUM(Price) AS Price, AVG(Price) AS AvgPrice
FROM (
 SELECT 
	Product, 
	Price,
	[Range] = 
		CASE WHEN Price BETWEEN 0 AND 1 THEN '1$'
			 WHEN Price BETWEEN 1 AND 2 THEN '2$'
			 WHEN Price BETWEEN 2 AND 3 THEN '3$'
		     WHEN Price BETWEEN 3 AND 4 THEN '4$'
		     WHEN Price BETWEEN 4 AND 5 THEN '5$'	
		END
 FROM @Products 
) a
GROUP BY GROUPING SETS((Product),([Range]))
HAVING [Range] IS NOT NULL
ORDER BY Product DESC, [Range] DESC

image

No comments:

Post a Comment