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.