Skip to Main Content

SQL & PL/SQL

Announcement

For appeals, questions and feedback, please email oracle-forums_moderators_us@oracle.com

Help needed in Tricky Group by

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.

Comments
Post Details
Added on Feb 19 2024
5 comments
131 views