CREATE TABLE #Temp
( ID INT,
  ProductID int,
  Quantity int
)
INSERT INTO #Temp
VALUES
( 1, 1, 23),
( 2, 1, 33),
( 3, 1, 6),
( 4, 1, 7),
( 5, 2, 15),
( 6, 2, 6),
( 7, 2, 13),
( 8, 3, 21),
( 9, 3, 16),
( 10, 4, 3),
( 11, 4, 11),
( 12, 4, 9),
( 13, 4, 14)

SELECT	ProductID,
		COUNT(*)
FROM	#Temp
WHERE	Quantity < 10
GROUP BY ProductID

SELECT	ProductID,
		COUNT(*)
FROM	#Temp
WHERE	Quantity < 15
GROUP BY ProductID

SELECT	ProductID,
		COUNT(*)
FROM	#Temp
WHERE	Quantity < 20
GROUP BY ProductID

SELECT	ProductID,
		COUNT(*)
FROM	#Temp
WHERE	Quantity < 25
GROUP BY ProductID

DROP TABLE #Temp

SELECT
	ProductId,
	SUM(CASE WHEN quantity < 10 THEN 1 ELSE 0 END) AS LessThan10,
	SUM(CASE WHEN quantity < 15 THEN 1 ELSE 0 END) AS LessThan15,
	SUM(CASE WHEN quantity < 20 THEN 1 ELSE 0 END) AS LessThan20,
	SUM(CASE WHEN quantity < 25 THEN 1 ELSE 0 END) AS LessThan25
FROM
	#temp 
WHERE
	quantity < 25
GROUP BY
	ProductId;

quote:

---

Originally posted by nevzab

James,

Can you explain what you mean by adding an index in this context please? I am new to SQL and indexing is a concept I understand in terms of speeding up queries but I wouldn't know how to implement an index.

Cheers,

Nev

---