One_Table_Scan.sql

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