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