DECLARE @Products TABLE (Product varchar(50),Category varchar(50),Price float)INSERT INTO @ProductsSELECT 'Sugar','Groceries',2.35UNION SELECT 'Bread','Groceries',1.09UNION SELECT 'Ecomonist','Magazines',4.99UNION SELECT 'FT','Magazines',2.99SELECT Product,Category, SUM(Price) AS Price, COUNT(*) AS [Count]FROM @ProductsGROUP BY GROUPING SETS((Product),(Category),())ORDER BY Product DESC, Category DESC, Price DESC, [Count] DESCSELECT Product,[Range], SUM(Price) AS Price, AVG(Price) AS AvgPriceFROM (SELECTProduct,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$'ENDFROM @Products) aGROUP BY GROUPING SETS((Product),([Range]))HAVING [Range] IS NOT NULLORDER BY Product DESC, [Range] DESC
Thursday, 9 February 2012
SQL Server GROUPING SETS
Subscribe to:
Post Comments (Atom)
No comments:
Post a Comment