Oracle SQL, Calculation not going as planned-CodePudding

Consider the following situation,

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

Here is the desired Output

Here is the SQL Fiddle

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

CodePudding user response：

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

COLOR	TOT_2010	TOT_2016
Green	16	7
Red	14	9

db<>fiddle here