I want to query for a count of a WHERE clause 1 and another count of WHERE clause 2. I have a simplified query result for my booking app: I want to 3 columns in total, first being a field called Token, second the count of Pending grouped by renter, and finally the count Overdue field grouped also by renter.

How to query the count of two fields by two different clauses at the same time?

I want to query for a count of a WHERE clause 1 and another count of WHERE clause 2. I have a simplified query result for my booking app:

Token   Pending    OverDue
---------------------------------
ITB001    1          23
IASFSA    8          4
WERSFD    7          1
XVCSFD    2          11
RERSDF    2          7
XVCLKD    1          8
MKB114    1          11

I want to 3 columns in total, first being a field called Token, second the count of Pending grouped by renter, and finally the count Overdue field grouped also by renter.

For the queries for Token and the count of Pending, I set the clause for any items that has an endDate(a.k.a due date) later than today’s date by 1 day:

SELECT token, COUNT(renter) FROM booking WHERE endDate >= CAST (current_date + 1 AS timestamp) AND endDate <  CAST (current_date + 2 AS timestamp) GROUP BY renter, token

The second column would be a similar query, but the endDate later than today’s date by 1 day. How do I query the count of Pending and Overdue group by renter at the same time?

Answer

Is this what you want?

SELECT token, COUNT(*),
       SUM(CASE WHEN enddate = current_date + interval '1 day' THEN 1 ELSE 0 END)
FROM booking 
GROUP BY token;

You have inconsistent database tags, but I’ve used a Postgres-like syntax for the date operations.

Advertisement

Answer