CODEWITHSUNDEEP
sqloracle-databaseaggregation
Spider
Spider

Reputation: 387

Aggregation between dates

I have the below query where I'm trying to calculate sum of salaries for over a period of year..

select sum(case when date_key between to_char(sysdate,'yyyymm')
                                  and to_char(add_months(sysdate,-12), 'yyyymm')
                then salary end) as annual_salary 
from employee
group by emp_key

When I execute the query I'm getting null's in the result set..

I actually have valid figures for salaries in employee table. Where am I going wrong?

Upvotes: 0

Views: 45

Answers (3)

user1364100
user1364100

Reputation: 

select sum(case when date_key between 
                                   to_char(add_months(sysdate,-12), 'yyyymm') and to_char(sysdate,'yyyymm')
                then salary end) as annual_salary 
from employee
group by emp_key

Upvotes: 0

Emmanuel
Emmanuel

Reputation: 14209

Just invert the 2 bounds, they are not in the correct order:

...
between to_char(add_months(sysdate,-12), 'yyyymm')
   and to_char(sysdate,'yyyymm')

Upvotes: 2

Multisync
Multisync

Reputation: 8787

select 
sum(case when date_key between to_char(sysdate,'yyyymm') and to_char(add_months(sysdate,-12),'yyyymm') then salary else 0 end) as annual_salary 
from employee group by emp_key

CASE WHEN THEN expr1 ELSE expr2 END
if you don't have "ELSE expr2" Oracle thinks it's NULL

Upvotes: 0

Related Questions