CODEWITHSUNDEEP
postgresql
Parwinder Kumar
Parwinder Kumar

Reputation: 81

Postgres query to get rejected and accepted count for application based on limit

Need rejected and accepted count between (Date1, Date2)

UserName   Status    Datetime
---------------------------------    
User1        accepted      2018-06-02
User2        rejected      2019-07-20
User3        accepted      2019-09-20 
User4        rejected      2019-09-20 
User5        rejected      2019-09-15
User6        accepted      2019-10-22 
User7        accepted      2019-10-23 

UserName     Status       Datetime
---------------------------------    
User1        accepted      2018-06-02
User2        rejected      2019-07-20
User3        accepted      2019-09-20 
User4        rejected      2019-09-20 
User5        rejected      2019-09-15
User6        accepted      2019-10-22 
User7        accepted      2019-10-23

if date between (2018-05-01) to (2019-10-30)

Year     Month       Accepted       Rejected
---------------------------------------------   
2018     June            1             0
2019     July            0             1
2019     September       1             2
2019     October         2             0

if date between (2019-05-01) to (2019-10-30)

Year     Month       Accepted       Rejected
---------------------------------------------   
2019     July            0             1
2019     September       1             2
2019     October         2             0

Upvotes: 0

Views: 196

Answers (1)

user330315
user330315

Reputation:

You can use conditional aggregation for that:

select to_char(datetime, 'yyyy-mm') as year_month,
       count(*) filter (where status = 'accepted') as accepted,       
       count(*) filter (where status = 'rejected') as rejected
from the_table
where datetime between date '2018-05-01' and date '2019-10-30'
group by to_char(datetime, 'yyyy-mm')
order by 1;

Note that if "datetime" is indeed a timestamp column (rather than a date as shown) you should avoid between and use explicit >= and < operators:

where datetime >= timestamp '2018-05-01 00:00:00' 
      datetime < timestamp '2019-10-31 00:00:00' -- one day after the intended end date

Upvotes: 2

Related Questions