That doesn't work unless I put MCHDC on the group by
Otherwise, I got error message:
column MCHDC or expression in SELECT list not valid.
Should I think about sub query?
How about this?
, SUM(QTYGD) AS Pieces
, SUM(CASE WHEN MCHDC = '' THEN 0 ELSE ELAP1 END) AS DOWN
, SUM(CASE WHEN MCHDC <> '' THEN 0 ELSE ELAP1 END) AS UP
you could define the summation as dec 9,2 or something along those lines. Also, instead of then 0, try using then null. It can really help improve your performance as a 0 is added to your summation, whereas, a null is ignored. Though you may have to account for having a null value returned or use the values or coalesce clause to force a 0 when the value is null.