'Perform two sums with different conditions

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 .
ID Value Date1 Date2
01 01 2022-02-15 2022-02-15
01 01 2022-02-15 2022-02-19
01 03 2022-02-15 2022-02-17
01 02 2022-02-16 2022-02-20
01 01 2022-02-16 2022-02-22
01 04 2022-02-16 2022-02-28

Expected outcome:

ID Date1 SUM1 SUM2
01 2022-02-15 05 04
01 2022-02-16 07 05

  • 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?

sqlpostgresqlgroup-byaggregate-functions


Solution 1:[1]

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

Sources

This article follows the attribution requirements of Stack Overflow and is licensed under CC BY-SA 3.0.

Source: Stack Overflow

Solution Source
Solution 1 D-Shih

Related Questions