Home » SQL & PL/SQL » SQL & PL/SQL » Help needed in Tricky Group by (oracle 19c)
Help needed in Tricky Group by [message #689600] Mon, 19 February 2024 11:52 Go to next message
born2achieve
Messages: 9
Registered: July 2012
Junior Member
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.
Re: Help needed in Tricky Group by [message #689601 is a reply to message #689600] Mon, 19 February 2024 13:24 Go to previous messageGo to next message
mathguy
Messages: 108
Registered: January 2023
Senior Member
Asked and answered on Oracle Forums: https://forums.oracle.com/ords/apexds/post/help-needed-in-tricky-group-by-3882
Re: Help needed in Tricky Group by [message #689604 is a reply to message #689601] Tue, 20 February 2024 10:13 Go to previous message
born2achieve
Messages: 9
Registered: July 2012
Junior Member
Yes, than you.
Previous Topic: Traverse CLOB with Connect By
Next Topic: Query having multiple subqueries to be simplified
Goto Forum:
  


Current Time: Thu May 23 11:45:29 CDT 2024