title: Aggregating Columns in a PIVOT
body: <p>The following query uses PIVOT to show how many times each quantity of a given product was ordered for quantities 1 thru 6.</p>
  
  <p>The results are too granular, I would like the columns to reflect ranges e.g. [1-2], [3-4], [5-6] by summing the results. </p>
  
  <p>How do I do this?</p>
  
  <pre><code>WITH Quantities AS
  (
  SELECT ProductID, Quantity
  FROM QuantityTest
  )
  SELECT * FROM Quantities
  PIVOT(COUNT(Quantity) FOR Quantity IN ([1],[2],[3],[4],[5],[6]))AS Pvt;
  
  
  
  CREATE TABLE [dbo].[QuantityTest]
  (
  [ProductID] [int] NOT NULL,
  [Quantity] [int] NOT NULL
  ) 
  GO
  INSERT [dbo].[QuantityTest] ([ProductID], [Quantity]) VALUES (76, 2)
  GO
  INSERT [dbo].[QuantityTest] ([ProductID], [Quantity]) VALUES (77, 3)
  GO
  INSERT [dbo].[QuantityTest] ([ProductID], [Quantity]) VALUES (78, 6)
  GO
  INSERT [dbo].[QuantityTest] ([ProductID], [Quantity]) VALUES (71, 5)
  GO
  INSERT [dbo].[QuantityTest] ([ProductID], [Quantity]) VALUES (72, 4)
  GO
  INSERT [dbo].[QuantityTest] ([ProductID], [Quantity]) VALUES (73, 2)
  GO
  INSERT [dbo].[QuantityTest] ([ProductID], [Quantity]) VALUES (74, 3)
  GO
  INSERT [dbo].[QuantityTest] ([ProductID], [Quantity]) VALUES (14, 3)
  GO
  INSERT [dbo].[QuantityTest] ([ProductID], [Quantity]) VALUES (16, 1)
  GO
  INSERT [dbo].[QuantityTest] ([ProductID], [Quantity]) VALUES (9, 5)
  GO
  INSERT [dbo].[QuantityTest] ([ProductID], [Quantity]) VALUES (12, 3)
  GO
  INSERT [dbo].[QuantityTest] ([ProductID], [Quantity]) VALUES (11, 6)
  GO
  INSERT [dbo].[QuantityTest] ([ProductID], [Quantity]) VALUES (62, 3)
  GO
  INSERT [dbo].[QuantityTest] ([ProductID], [Quantity]) VALUES (58, 4)
  GO
  INSERT [dbo].[QuantityTest] ([ProductID], [Quantity]) VALUES (45, 4)
  GO
  INSERT [dbo].[QuantityTest] ([ProductID], [Quantity]) VALUES (43, 3)
  GO
  INSERT [dbo].[QuantityTest] ([ProductID], [Quantity]) VALUES (47, 3)
  GO
  INSERT [dbo].[QuantityTest] ([ProductID], [Quantity]) VALUES (12, 6)
  GO
  INSERT [dbo].[QuantityTest] ([ProductID], [Quantity]) VALUES (15, 5)
  GO
  INSERT [dbo].[QuantityTest] ([ProductID], [Quantity]) VALUES (42, 4)
  GO
  INSERT [dbo].[QuantityTest] ([ProductID], [Quantity]) VALUES (75, 4)
  GO
  INSERT [dbo].[QuantityTest] ([ProductID], [Quantity]) VALUES (78, 5)
  GO
  INSERT [dbo].[QuantityTest] ([ProductID], [Quantity]) VALUES (11, 6)
  GO
  INSERT [dbo].[QuantityTest] ([ProductID], [Quantity]) VALUES (41, 3)
  GO
  INSERT [dbo].[QuantityTest] ([ProductID], [Quantity]) VALUES (76, 2)
  GO
  INSERT [dbo].[QuantityTest] ([ProductID], [Quantity]) VALUES (73, 2)
  GO
  INSERT [dbo].[QuantityTest] ([ProductID], [Quantity]) VALUES (16, 6)
  GO
  INSERT [dbo].[QuantityTest] ([ProductID], [Quantity]) VALUES (77, 5)
  GO
  INSERT [dbo].[QuantityTest] ([ProductID], [Quantity]) VALUES (8, 5)
  GO
  INSERT [dbo].[QuantityTest] ([ProductID], [Quantity]) VALUES (64, 2)
  GO
  INSERT [dbo].[QuantityTest] ([ProductID], [Quantity]) VALUES (70, 2)
  GO
  INSERT [dbo].[QuantityTest] ([ProductID], [Quantity]) VALUES (30, 2)
  GO
  INSERT [dbo].[QuantityTest] ([ProductID], [Quantity]) VALUES (54, 6)
  GO
  INSERT [dbo].[QuantityTest] ([ProductID], [Quantity]) VALUES (25, 3)
  GO
  INSERT [dbo].[QuantityTest] ([ProductID], [Quantity]) VALUES (62, 6)
  GO
  INSERT [dbo].[QuantityTest] ([ProductID], [Quantity]) VALUES (65, 1)
  GO
  INSERT [dbo].[QuantityTest] ([ProductID], [Quantity]) VALUES (68, 5)
  GO
  INSERT [dbo].[QuantityTest] ([ProductID], [Quantity]) VALUES (53, 4)
  GO
  INSERT [dbo].[QuantityTest] ([ProductID], [Quantity]) VALUES (56, 3)
  GO
  INSERT [dbo].[QuantityTest] ([ProductID], [Quantity]) VALUES (63, 3)
  GO
  INSERT [dbo].[QuantityTest] ([ProductID], [Quantity]) VALUES (32, 5)
  GO
  INSERT [dbo].[QuantityTest] ([ProductID], [Quantity]) VALUES (58, 2)
  GO
  INSERT [dbo].[QuantityTest] ([ProductID], [Quantity]) VALUES (29, 3)
  GO
  INSERT [dbo].[QuantityTest] ([ProductID], [Quantity]) VALUES (22, 1)
  GO
  INSERT [dbo].[QuantityTest] ([ProductID], [Quantity]) VALUES (49, 4)
  GO
  INSERT [dbo].[QuantityTest] ([ProductID], [Quantity]) VALUES (60, 3)
  GO
  INSERT [dbo].[QuantityTest] ([ProductID], [Quantity]) VALUES (26, 6)
  GO
  INSERT [dbo].[QuantityTest] ([ProductID], [Quantity]) VALUES (33, 4)
  GO
  INSERT [dbo].[QuantityTest] ([ProductID], [Quantity]) VALUES (38, 4)
  GO
  INSERT [dbo].[QuantityTest] ([ProductID], [Quantity]) VALUES (66, 6)
  GO
  INSERT [dbo].[QuantityTest] ([ProductID], [Quantity]) VALUES (55, 4)
  GO
  INSERT [dbo].[QuantityTest] ([ProductID], [Quantity]) VALUES (60, 1)
  GO
  INSERT [dbo].[QuantityTest] ([ProductID], [Quantity]) VALUES (72, 2)
  GO
  INSERT [dbo].[QuantityTest] ([ProductID], [Quantity]) VALUES (75, 1)
  GO
  INSERT [dbo].[QuantityTest] ([ProductID], [Quantity]) VALUES (14, 4)
  GO
  INSERT [dbo].[QuantityTest] ([ProductID], [Quantity]) VALUES (16, 4)
  GO
  INSERT [dbo].[QuantityTest] ([ProductID], [Quantity]) VALUES (77, 1)
  GO
  INSERT [dbo].[QuantityTest] ([ProductID], [Quantity]) VALUES (71, 4)
  GO
  INSERT [dbo].[QuantityTest] ([ProductID], [Quantity]) VALUES (73, 6)
  GO
  INSERT [dbo].[QuantityTest] ([ProductID], [Quantity]) VALUES (78, 3)
  GO
  INSERT [dbo].[QuantityTest] ([ProductID], [Quantity]) VALUES (11, 3)
  GO
  INSERT [dbo].[QuantityTest] ([ProductID], [Quantity]) VALUES (73, 2)
  GO
  INSERT [dbo].[QuantityTest] ([ProductID], [Quantity]) VALUES (7, 5)
  GO
  INSERT [dbo].[QuantityTest] ([ProductID], [Quantity]) VALUES (15, 6)
  GO
  INSERT [dbo].[QuantityTest] ([ProductID], [Quantity]) VALUES (77, 3)
  GO
  INSERT [dbo].[QuantityTest] ([ProductID], [Quantity]) VALUES (12, 3)
  GO
  INSERT [dbo].[QuantityTest] ([ProductID], [Quantity]) VALUES (75, 6)
  GO
  INSERT [dbo].[QuantityTest] ([ProductID], [Quantity]) VALUES (78, 4)
  GO
  INSERT [dbo].[QuantityTest] ([ProductID], [Quantity]) VALUES (9, 1)
  GO
  INSERT [dbo].[QuantityTest] ([ProductID], [Quantity]) VALUES (76, 5)
  GO
  INSERT [dbo].[QuantityTest] ([ProductID], [Quantity]) VALUES (64, 2)
  GO
  INSERT [dbo].[QuantityTest] ([ProductID], [Quantity]) VALUES (53, 4)
  GO
  INSERT [dbo].[QuantityTest] ([ProductID], [Quantity]) VALUES (32, 2)
  GO
  INSERT [dbo].[QuantityTest] ([ProductID], [Quantity]) VALUES (56, 5)
  GO
  INSERT [dbo].[QuantityTest] ([ProductID], [Quantity]) VALUES (68, 2)
  GO
  INSERT [dbo].[QuantityTest] ([ProductID], [Quantity]) VALUES (66, 1)
  GO
  INSERT [dbo].[QuantityTest] ([ProductID], [Quantity]) VALUES (10, 4)
  GO
  INSERT [dbo].[QuantityTest] ([ProductID], [Quantity]) VALUES (73, 3)
  GO
  INSERT [dbo].[QuantityTest] ([ProductID], [Quantity]) VALUES (78, 3)
  GO
  INSERT [dbo].[QuantityTest] ([ProductID], [Quantity]) VALUES (75, 2)
  GO
  INSERT [dbo].[QuantityTest] ([ProductID], [Quantity]) VALUES (56, 4)
  GO
  INSERT [dbo].[QuantityTest] ([ProductID], [Quantity]) VALUES (53, 3)
  GO
  INSERT [dbo].[QuantityTest] ([ProductID], [Quantity]) VALUES (60, 5)
  GO
  INSERT [dbo].[QuantityTest] ([ProductID], [Quantity]) VALUES (65, 3)
  GO
  INSERT [dbo].[QuantityTest] ([ProductID], [Quantity]) VALUES (15, 3)
  GO
  INSERT [dbo].[QuantityTest] ([ProductID], [Quantity]) VALUES (30, 3)
  GO
  INSERT [dbo].[QuantityTest] ([ProductID], [Quantity]) VALUES (7, 2)
  GO
  INSERT [dbo].[QuantityTest] ([ProductID], [Quantity]) VALUES (11, 1)
  GO
  INSERT [dbo].[QuantityTest] ([ProductID], [Quantity]) VALUES (54, 4)
  GO
  INSERT [dbo].[QuantityTest] ([ProductID], [Quantity]) VALUES (12, 3)
  GO
  INSERT [dbo].[QuantityTest] ([ProductID], [Quantity]) VALUES (29, 2)
  GO
  INSERT [dbo].[QuantityTest] ([ProductID], [Quantity]) VALUES (55, 2)
  GO
  INSERT [dbo].[QuantityTest] ([ProductID], [Quantity]) VALUES (61, 3)
  GO
  INSERT [dbo].[QuantityTest] ([ProductID], [Quantity]) VALUES (70, 2)
  GO
  INSERT [dbo].[QuantityTest] ([ProductID], [Quantity]) VALUES (26, 3)
  GO
  INSERT [dbo].[QuantityTest] ([ProductID], [Quantity]) VALUES (64, 2)
  GO
  INSERT [dbo].[QuantityTest] ([ProductID], [Quantity]) VALUES (66, 2)
  GO
  INSERT [dbo].[QuantityTest] ([ProductID], [Quantity]) VALUES (25, 2)
  GO
  INSERT [dbo].[QuantityTest] ([ProductID], [Quantity]) VALUES (16, 4)
  GO
  INSERT [dbo].[QuantityTest] ([ProductID], [Quantity]) VALUES (68, 3)
  GO
  </code></pre>