Results 1 to 7 of 7
  1. #1
    Join Date
    Sep 2003
    Posts
    32

    Unanswered: A query with group problem

    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?

  2. #2
    Join Date
    Feb 2008
    Location
    Japan
    Posts
    3,483
    How about this?

    Code:
    select TRPRD
         , 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
      from rmsfiles84.TP_PA 
     group by
           TRPRD
    ;

  3. #3
    Join Date
    Sep 2003
    Posts
    32

    Doesn't work

    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?

    Code:
     How about this?
    
    
    Code:
    select TRPRD
         , 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
      from rmsfiles84.TP_PA 
     group by
           TRPRD, MCHDC
    ;

  4. #4
    Join Date
    Sep 2003
    Posts
    32

    Actually, it worked

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

  5. #5
    Join Date
    Sep 2003
    Posts
    32

    One Little Problem

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

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

  6. #6
    Join Date
    Dec 2007
    Location
    Richmond, VA
    Posts
    1,328
    Provided Answers: 5
    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

  7. #7
    Join Date
    Sep 2003
    Posts
    32

    Thanks

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

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •