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

score 31 · Accepted Answer · answered Jun 04 '14 at 06:47

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

score 4 · Answer 2 · edited May 04 '20 at 18:24

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.

Postgres Aggregating conditional sum

2 Answers2