Postgres Aggregating conditional sum

Question

I am trying to sum-aggregate conditional products for total weight of an order (I hope that makes sense). I get error:

ERROR: aggregate function calls cannot be nested
LINE 6: , SUM ( CASE WHEN pc.type = 'TEES' THEN (SUM (opd.qt...

This is query excerpt:

SELECT DISTINCT
         o.work_order_number dn
       , SUM(opd.qty) units
       , SUM (CASE  WHEN pc.type = 'TEES' THEN (SUM (opd.qty) * .75)
            WHEN pc.type = 'JERSEYS' THEN (SUM (opd.qty) * 1.5)
        END) AS weight

Lennart - Slava Ukraini · Accepted Answer · 2014-06-04 06:47:18Z

33

Try:

SELECT o.work_order_number dn 
     , SUM(opd.qty) units 
     , SUM ( opd.qty * CASE pc.type 
                           WHEN 'TEES' THEN 0.75 
                           WHEN 'JERSEYS' THEN 1.5 
                       END ) AS weight
FROM ...
GROUP BY o.work_order_number

answered Jun 4, 2014 at 6:47

Lennart - Slava Ukraini

7,2111 gold badge23 silver badges37 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Lennart - Slava Ukraini · Accepted Answer · 2020-05-04 18:24:48Z

4

Well what you can do is nest your select statement E.g

select sum(weight),sum(etc) 
from (
    SELECT DISTINCT o.work_order_number dn 
                 , (opd.qty) units 
                 , ( CASE WHEN pc.type = 'TEES' THEN ((opd.qty) * .75) 
                          WHEN pc.type = 'JERSEYS' THEN ((opd.qty) * 1.5) END) AS weight)
).

So first select statement handles your case statement and second select statement sums up your fields.

edited May 4, 2020 at 18:24

Lennart - Slava Ukraini

7,2111 gold badge23 silver badges37 bronze badges

answered Jun 4, 2014 at 6:45

JustLearning

3,3423 gold badges37 silver badges58 bronze badges

Collectives™ on Stack Overflow

Postgres Aggregating conditional sum

2 Answers 2

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related