SUM - Aggregate Metodu

Örnek - SUM + OVER (ORDER BY)

Elimizde şöyle bir tablo olsun. (take - give) sonucunu toplayarak gitmek isteyelim

id      Usage         take  give 
------  ------------  ----  ----  
  1     Selling AAA   10    0         
  2     Purchase 1    0     40    
  3     Selling BBB   50    0     

Şöyle yaparız

select t.*,
       sum(take - give) over (order by id) as balance
from transaction t

Çıktı olarak şunu alırız

id      Usage         take  give  balance
------  ------------  ----  ----  --------
  1     Selling AAA   10    0     10    
  2     Purchase 1    0     40    -30
  3     Selling BBB   50    0     20

Örnek - SUM + FILTER + GROUP BY
Normalde iki tane iç içe select yapmak gerekir.

SELECT TASK, METHOD, SUM(SUM_DURATION) AS SUM_DURATION, 
SUM(SUM_DURATION_EUR) AS SUM_DURATION_EUR,
SUM(SUM_DURATION_USA) AS SUM_DURATION_USA
FROM (SELECT TASK, METHOD, SUM(DURATION) AS SUM_DURATION,
       CASE WHEN LOCATION = 'EUR' THEN SUM(DURATION) END AS SUM_DURATION_EUR,
       CASE WHEN LOCATION = 'USA' THEN SUM(DURATION) END AS SUM_DURATION_USA
   FROM EXAMPLE_TABLE
   GROUP BY 1,2,LOCATION) T
GROUP BY 1,2

Filtered aggregation kullanarak şöyle yaparız

SELECT task, method, 
       sum(sum_duration) as sum_duration, 
       sum(sum_duration) filter (where location = 'EUR') as sum_duration_eur, 
       sum(sum_duration) filter (where location = 'USA') as sum_duration_usa 
FROM example_table
GROUP BY task, method

Çıktı olarak şunu alırız

Task | Method | Sum_Duration | Sum_Duration_EUR | Sum_Duration_USA
Run  |   A    |      40      |      28          |       12
Run  |   B    |      10      |      10          |       null
Walk |   A    |      9       |      9           |       null