select TRPRD,SUM(QTYGD) AS Pieces,(CASE WHEN MCHDC='' THEN 0 ELSE SUM(ELAP1) END ) AS DOWN ,(CASE WHEN MCHDC<>'' THEN 0 ELSE SUM(ELAP1) END) AS UP, MCHDC
from rmsfiles84.TP_PA
group by TRPRD,MCHDC

TRPRD-------- PIECES--------- DOWN--- UP--- MCHDC
AALX -------- 0------ 1--- 0--- 112
AALX-------- 5------- 0--- 3---

But my goal is to group by TRAPRD to sum the pieces, down and up. But MCHDC is a field I need for case condition. How do you think I can do?

How about this?

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?

Sorry, I just found it worked. Thanks very much.

ROUND(SUM(CASE WHEN MCHDC='' THEN 0 ELSE ELAP1 END ),2)

The result is still with many decimal, The round doesn't work. Why?

Newer,
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.

Dave

I changed to Null, and I did feel the performance is different. Thanks