Oracle SQL, Calculation not going as planned

user_9592902 0
Viewed 16

Consider the following situation, DataSet

We have Apple that contains info of Apples Sold, Color gives Apple Color, Date gives the date it was sold.

I want to get Apple color, the sum of Apples sold after 2010, and the sum of apples sold after 2016 in the same row.

This is my code,

SELECT distinct Color, (CASE WHEN WHN >= to_date('01/01/2010','mm/dd/yyyy') 
THEN SUM(Apple) over (partition by Color) end) as tot_2010,
(CASE WHEN WHN >= to_date('01/01/2016','mm/dd/yyyy') 
THEN SUM(Apple) over (partition by Color) end) as tot_2016
from temp;

Now, this is the output I get

Output

Here is the desired Output

Desired Output

Here is the SQL Fiddle

http://sqlfiddle.com/#!4/86b932/7

oracle sql
1 Answers

You want to use conditional aggregation:

SELECT color,
       SUM(CASE WHEN whn >= DATE '2010-01-01' THEN apple END) AS tot_2010,
       SUM(CASE WHEN whn >= DATE '2016-01-01' THEN apple END) AS tot_2016
FROM   temp
GROUP BY color;

Which, for your sample data:

create table temp(Apple, Color,  WHN) AS
SELECT 3, 'Red',   DATE '2012-01-05' FROM DUAL UNION ALL
SELECT 9, 'Green', DATE '2014-01-11' FROM DUAL UNION ALL
SELECT 8, 'Red',   DATE '2017-10-07' FROM DUAL UNION ALL
SELECT 1, 'Red',   DATE '2021-02-10' FROM DUAL UNION ALL
SELECT 7, 'Green', DATE '2020-08-04' FROM DUAL UNION ALL
SELECT 2, 'Red',   DATE '2013-03-10' FROM DUAL;

Outputs:

COLOR TOT_2010 TOT_2016
Green 16 7
Red 14 9

db<>fiddle here

user_15092640
Related