Postgres Aggregating conditional sum
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 weightFROM ...GROUP BY o.work_order_number
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.