Post reply

Help needed in ORACLE Group by

  • February 19, 2024 at 5:57 pm

    Go to Answer

    #4381251

    Hello, working in Oracle Database 19c Enterprise Edition. Below is my sample data

    with Sample( ID, QTY,STATUS,LAST_UPDATED_DATE) as 
              (
                 select 1, 2400, null, null from dual union all
                 select 1, 2400, null, null from dual union all
                 select 1, 2400, null, null from dual union all
                 select 1, 2400, 'Received', sysdate from dual          
              
              )

    I need the output as following .

    select 1, 2400 as Received, 7200 as "Not Received", '2/19/2024' as Last_Updated_Date  from dual;

    Am not sure how can i do this in group by. below the way i tried. but its giving two rows. please show me a sample query to bring the results in one row like the above query result.

    SELECT Id,
             CASE WHEN STATUS = 'Received' THEN SUM (QTY) ELSE 0 END
                 AS Received,
             CASE WHEN STATUS IS NULL THEN SUM (QTY) ELSE 0 END
                 AS "Not Received"
        FROM Sample
    GROUP BY Id, Status;

    Thanks in advance for the replies.

  • February 19, 2024 at 6:12 pm

    Answer

    #4381266

    I'm not really familiar with Oracle, but this seems like something universal, so I will give it a try.

    SELECT Id,
           SUM(CASE WHEN STATUS = 'Received' THEN  QTY ELSE 0 END) AS Received,
           SUM(CASE WHEN STATUS IS NULL THEN QTY ELSE 0 END) AS "Not Received",
           MAX(LAST_UPDATED_DATE) AS Last_Updated_Date
    FROM SAMPLE
    GROUP BY Id;

  • February 20, 2024 at 5:48 pm

    #4381828

    thank you. yes, this worked. appreciated

Viewing 3 posts - 1 through 2 (of 2 total)

You must be logged in to reply to this topic. Login to reply