CODEWITHSUNDEEP
sqlnetezza
Kelvin
Kelvin

Reputation: 3

Select query with multiple sub queries for counts (Netezza SQL)

I am trying to create a report that gets counts at 3 periods of time: the previous month, that month last year, and year to date.

I previously used 3 separate queries like below while toggling the where clauses, but I want be able to combine all 3 into one query.

I've tried with case statements but couldn't seem to get that to work. FYI app_date is YYYY-MM-DD

Select count(application_id)
from application_data a
where to_char(app_date, 'YYYYMM' = to_char(current_date, 'YYYYMM')-1
--where to_char(app_date, 'YYYYMM' = to_char(current_date, 'YYYYMM')-101
--where to_char(app_date, 'YYYY') = to_char(current_date, 'YYYY') and to_char(app_date, 'YYYYMM') <> to_char(current_date, 'YYYYMM')

Sample data: 

App_ID          App_date  
123519          2018-02-17  
123521          2018-02-18  
123522          2018-02-19  
123523          2018-02-23  
123518          2019-01-15  
123546          2019-02-21  
123547          2019-02-22  
123548          2019-02-15  
123542          2019-02-02

Desired Result: 

LastMonth      YTD       YoY  
4               5         4

Upvotes: 0

Views: 250

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1271003

I think you want conditional aggregation:

Select sum(case when to_char(app_date, 'YYYYMM' = to_char(current_date, 'YYYYMM')-1 then 1 else 0 end),
       sum(case to_char(app_date, 'YYYYMM' = to_char(current_date, 'YYYYMM')-101 when then 1 else 0 end),
       sum(case when to_char(app_date, 'YYYY') = to_char(current_date, 'YYYY') and to_char(app_date, 'YYYYMM') <> to_char(current_date, 'YYYYMM') then 1 else 0 end)
from application_data a

Upvotes: 1

Related Questions