Home Multiple columns from aggregate function, but apply only to rows matching a WHERE clause

Multiple columns from aggregate function, but apply only to rows matching a WHERE clause

fdmillion
1#
fdmillion Published in 2018-01-12 23:44:59Z
 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.
clinomaniac
2#
 A CASE statement might be what you need to do it in a simple query. SELECT [order_id], SUM(CASE WHEN prod_id = 1 THEN qty ELSE 0) AS p1_tot, SUM(CASE WHEN prod_id = 2 OR prod_id = 3 THEN qty ELSE 0) AS p2and3_tot, SUM(CASE WHEN prod_id = 4 OR prod_id = 6 THEN qty ELSE 0) AS p4and6_tot FROM [orders] INNER JOIN [product_class] ON [orders].[prod_id] = [product_class].[id] WHERE [product_class].[class] = 2 GROUP BY [order_id]; 
 I took the sample data provided for the orders table and created the product_class table from the description in the 2nd paragraph of the question. Sample Data: create table #orders ( order_id int , prod_id int , qty int ) create table #product_class ( id int , class int ) insert into #orders values (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) insert into #product_class values (1, 1) , (2, 2) , (3, 2) , (4, 3) , (6, 3)  Answer: Starting with the first query written in the question as inspiration, I changed the reference to the product_class table to an inner join and added the class to the group by. After that, all that needs to happen is a pivot to get the classes to go across the columns instead of the rows. select b.order_id , isnull(b.[1], 0) as c1_tot , isnull(b.[2], 0) as c2_tot , isnull(b.[3], 0) as c3_tot from ( SELECT o.[order_id] , pc.class , SUM(o.[qty]) AS [c2] FROM [#orders] as o inner join #product_class as pc on o.prod_id = pc.id GROUP BY o.[order_id] , pc.class ) as a pivot (max(a.c2) for a.class in ([1], [2], [3])) as b  Output matches the desired output exactly.