combining different Aggregate functions with CASE

Question

I want to combine different sums for different periods in a query

i use the following query to get a sum.

SELECT table1.BRS,
Sum(CASE WHEN table2.DATUM BETWEEN :van1 AND :tot1 THEN (DBBASIS-CRBASIS) ELSE (0) END)    
FROM table1 INNER JOIN table2 ON (table1.id= table2.id)    
WHERE table2.DATUM BETWEEN :van1 AND :tot1 
group by BRS

and this works as itnented.

If i upgrade to a second sum

SELECT table1.BRS,
Sum(CASE WHEN table2.DATUM BETWEEN :van1 AND :tot1 THEN (DBBASIS-CRBASIS) ELSE (0) END),
Sum(CASE WHEN table2.DATUM BETWEEN :van2 AND :tot2 THEN (DBBASIS-CRBASIS) ELSE (0) END)    
FROM table1 INNER JOIN table2 ON (table1.id= table2.id)    
WHERE table2.DATUM BETWEEN :van1 AND :tot1 or  table2.DATUM BETWEEN :van2 AND :tot2 
group by BRS

I get a false positive with 2 duplicate columns, even if the data is different. The query uses the same Dates for the second sum. When i change the second Sum to a Max function, it uses the Second dates

How to combine the results of these 2 functions.

PS: Interbase cannot uses

Select * from (Select Sum from...) Join (Select Sum from...)

paul · Answer 1 · 2013-02-28T13:42:22.797

2

If I have understood your data model correctly, then:

SELECT  table1.BRS,
        Sum(t2a.DBBASIS-CRBASIS),
        Sum(t2b.DBBASIS-CRBASIS)
FROM    table1 LEFT JOIN table2 t2a ON (table1.id= t2a.id and t2a.DATUM BETWEEN :van1 AND :tot1)
        LEFT JOIN table2 t2b ON (table1.id = t2b.id and t2b.DATUM BETWEEN :van2 AND :tot2)
group by BRS

edit:

SELECT  t1a.BRS,
        Sum(t1a.DBBASIS-CRBASIS),
        Sum(t1b.DBBASIS-CRBASIS)
FROM    table2 left join table1 t1a ON (table2.id= t1a.id and table2.DATUM BETWEEN :van1 AND :tot1)
        LEFT JOIN table1 t1b ON (table2.id = t1b.id and table2.DATUM BETWEEN :van2 AND :tot2)
group by t1a.BRS

edited Feb 28 '13 at 13:42

answered Feb 28 '13 at 13:20

paul

21,653
1
53
54

the data (DBBASIS & CRBASIS) is in table1. thnx for you answer, i feel i got close but not there yet – r_j Feb 28 '13 at 13:38
thnx for the edit, i'm really close now but i get only a few result rows, while i need more, feels like its has something to do with the joins? – r_j Feb 28 '13 at 13:52
nevermind, i get an empty result table because if my dates are not overlapping, and it ony gives result for record that pass both periods – r_j Feb 28 '13 at 13:55

combining different Aggregate functions with CASE

1 Answers1