I need to perform two Sums with different conditions:

• First Sum: Must return the sum of values for each date1 and id (Group by date1 and id).
• Second Sum: Must return the sum of values for each date1 and id (Group by date1 and id), without the value corresponding to the smallest date2 .

Expected outcome:

• In sum2, for data1=2022-02-15, the value corresponding to data2=2022-02-15 was removed, as it is the smallest value of data2 in the grouping.

• In sum2 , for data1 = 2022-02-16, the value corresponding to data2=2022-02-20 was removed, as it is the smallest value of data2 in the grouping.

Any suggestions on how to get this result?

CodePudding user response：

Since partition by does not always play well with group by, you can use CTE to achieve the result:

with tmp as (
    select tv.*, first_value(value) over (partition by id, date1 order by date2) date2_value
    from table_views tv
)
select id, date1, sum(value) 
from tmp
group by id, date1
union all
select id, date1, sum(value) - min(date2_value)
from tmp
group by id, date1;

Here is the db_fiddle.

I've used min here, but that is arbitraty and any other would work: max, avg etc.

CodePudding user response：

You can try to use LEAD window function in subquery which will get the next value per Date1 (that will ignore the smallest per Date1).

Then do sum on value column & LEAD window function column.

SELECT ID,
       Date1,
       SUM(Value) SUM1,
       SUM(n_val) SUM2
FROM (
    SELECT *,
           LEAD(Value) OVER(PARTITION BY ID,Date1 ORDER BY Date2) n_val
    FROM table_views 
) t1
GROUP BY ID,Date1;

sqlfiddle