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

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#
clinomaniac Reply to 2018-01-13 00:31:28Z

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];
tarheel
3#
tarheel Reply to 2018-01-13 20:46:37Z

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.

You need to login account before you can post.

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

© 2016 Powered by mzan.com design MATCHINFO