Postgres Aggregating conditional sum

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.


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