Slightly Advanced SQL Aggregation

I hold myself as a programmer first, and a blogger second: so I hope you don't mind if I post a bit on a few SQL techniques I used this week.

Here's today's example:
I need a table of information containing a list of clients with their ID that shows counts by order type: 1,2,3, and OTHER.

Sounds like you should just do multiple select statements for each client right?

	SELECT Client_ID,
	(SELECT COUNT(1) FROM Orders OS WHERE OS.Client_ID = O.Client_ID AND OS.Order_Type = 1) Type_1,
	(SELECT COUNT(1) FROM Orders OS WHERE OS.Client_ID = O.Client_ID AND OS.Order_Type = 2) Type_2,
	(SELECT COUNT(1) FROM Orders OS WHERE OS.Client_ID = O.Client_ID AND OS.Order_Type = 3) Type_3,
	(SELECT COUNT(1) FROM Orders OS WHERE OS.Client_ID = O.Client_ID AND (OS.Order_Type NOT IN (1,2,3) OR OS.Order_Type IS NULL)) Other
	FROM Orders O
	GROUP BY Client_ID

view raw Such_Subquery.wow.sql hosted with ❤ by GitHub

WRONG. This query is very hard to maintain and does at least 4 table scans per client. Yuck.

Try this instead:

	SELECT Client_ID
	COUNT(CASE WHEN Order_Type IN (1) THEN 1 ELSE NULL END) Type_1,
	COUNT(CASE WHEN Order_Type IN (2) THEN 1 ELSE NULL END) Type_2,
	COUNT(CASE WHEN Order_Type IN (3) THEN 1 ELSE NULL END) Type_3,
	COUNT(CASE WHEN Order_Type NOT IN (1,2,3) OR Order_Type IS NULL THEN 1 ELSE NULL END) Other
	FROM Orders
	GROUP BY Client_ID

view raw One_Table_Scan.sql hosted with ❤ by GitHub

Look at that maintainable easy to read code (unless you didn't know you could put a case statement inside of an aggregate function.) Now step up your ante, find the latest Order_Date split out by types using the mentioned technique using the Max function.

(hint, here's the answer:)

	SELECT Client_ID
	MAX(CASE WHEN Order_Type IN (1) THEN Order_Date ELSE NULL END) Type_1,
	MAX(CASE WHEN Order_Type IN (2) THEN Order_Date ELSE NULL END) Type_2,
	MAX(CASE WHEN Order_Type IN (3) THEN Order_Date ELSE NULL END) Type_3,
	MAX(CASE WHEN Order_Type NOT IN (1,2,3) OR Order_Type IS NULL THEN Order_Date ELSE NULL END) Other
	FROM Orders
	GROUP BY Client_ID

view raw Order_Date_One_Table_Scan.sql hosted with ❤ by GitHub

Sure, this is nothing special, but to someone who wants to increase the size of their Developer Toolbox, don't forget about speed and efficiency.

Have fun,
~Josh