Count on a column with a conditional statement in SQL?-CodePudding

I want a table as this:

cycle_end_date | count(user_id) | count(repaid_user_id) | event_ts | repayment_ts 
15th Jan       |    20          | 15                    | 23rd Jan  | 25th Jan
15th Jan       |    30          |  30                   | 24th Jan  | 24th Jan

I am using the following query:

    select
    date(cycle_end_date) as cycle_end_date
    ,date(payload_event_timestamp    interval '5 hours 30 minutes') as event_date
    ,d.payment_date
   ,count( s.user_id) as count
   ,count(case s.user_id when d.payment_date < event_date then 1 else 0 end) as repaid_users

d.payment_date is taken from another query -

case when ots_created_at is null then 
            current_date else date(ots_created_at   interval ' 5 hours 30 minutes') 
            end as payment_date

this is giving me an error : ProgrammingError: operator does not exist: character varying = boolean

Basically I want the count of users who repaid on the day event was triggered. which is basically if event date is equal to repayment date we can find out that these many users paid on that event date. How to find this?

CodePudding user response：

You have attempted to use a "short form" of case expression which starts like this:

case s.user_id when d.payment_date

For this format to be valid the syntax after when assumes an equal comparison (e.g. when d.payment_date assumes =) and in this short form it can only be an equality operator that is assumed. For more complex comparisons (less than, not equal, etc.) it is necessary to use the longer format of a case expression, like this:

case when d.payment_date < event_date then user_id end

Note that the aggregate function COUNT() increments for every non-null value, so to avoid counting any unwanted conditions do NOT return any value using else. e.g.

count(case when d.payment_date < event_date then user_id end) as col1

or, if you prefer to be more explicit in the query code, use else to return NULL which will not be counted, e.g.

count(case when d.payment_date < event_date then user_id else NULL end) as col1

nb: Instead of returning a column value you could use a constant like this:

count(case when d.payment_date < event_date then 1 end) as col1