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

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

user2408
1#
user2408 Published in July 21, 2018, 9:49 am

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.

You need to login account before you can post.

About| Privacy statement| Terms of Service| Advertising| Contact us| Help| Sitemap|
Processed in 0.314292 second(s) , Gzip On .

© 2016 Powered by mzan.com design MATCHINFO