Home Multiple columns from aggregate function, but apply only to rows matching a WHERE clause
 Sample dataset: +----------+---------+-----+ | order_id | prod_id | qty | +----------+---------+-----+ | 1 | 1 | 2 | | 1 | 1 | 4 | | 1 | 3 | 1 | | 2 | 1 | 1 | | 2 | 2 | 2 | | 3 | 4 | 1 | | 3 | 4 | 2 | | 3 | 2 | 3 | | 3 | 3 | 5 | | 4 | 1 | 2 | | 4 | 2 | 3 | | 4 | 3 | 3 | | 4 | 4 | 3 | | 4 | 6 | 2 | +----------+---------+-----+  What I'm trying to do is build a "report" view, that will aggregate certain totals based on the product code. So for example, I want one column with totals by order for product code 1, another column with aggregate total for products 2 and 3, and one for product code 4 and 6. Note that the values for the product codes are coming from a product class table, so I'm actually looking to get all products in class 1, which would be product 1, then all products in class 2, which would be products 2 and 3, and then all products in class 3, which would be products 4 and 6. +----------+--------+--------+--------+ | order_id | c1_tot | c2_tot | c3_tot | +----------+--------+--------+--------+ | 1 | 6 | 1 | 0 | | 2 | 1 | 2 | 0 | | 3 | 0 | 8 | 3 | | 4 | 2 | 6 | 5 | +----------+--------+--------+--------+  I can do this for one column using a WHERE clause like this: SELECT [order_id], SUM([qty]) AS [c2] FROM [orders] WHERE [prod_id] IN (SELECT [id] FROM [product_class] WHERE [class] = 2) GROUP BY [order_id]  The other approach I came up with was a stored function, but this seems a long-winded way to do it: SELECT DISTINCT [order_id], total_products_by_class([order_id],1) AS [c1_tot], total_products_by_class([order_id],2) AS [c2_tot], total_products_by_class([order_id],3) AS [c3_tot] FROM [orders]  I suspect something could theoretically be done with analytic functions?... Is there a way to do this without a stored function in something suitable for a view? Update: make clearer about need to pull in values for products from a subquery.