SQL - how to get 0 in SQL as counting result?-CodePudding

I created SQL query which should count the number of e-mails sent after 20.12.2022 for each user_id from ACTIONQUEUE table. My query counts everything correctly for all users, but it doesn't returns 0 in the table for the users with ID 3 and 4, who haven't received any e-mails after 20.12.2022. How should I modify my query to fill in the RESULT table with 0 for these users?

ACTIONQUEUE table:

| User_ID  | Sent_date  |
| -------- | --------   |
| 2        | 31.12.2022 |
| 3        | 17.01.2023 |
| 2        | 01.01.2023 |
| 1        | 02.01.2023 |
| 5        | 31.12.2022 |
| 5        | 03.01.2023 |
| 4        | 15.12.2022 |

My SQL query:

SELECT USER_ID, COUNT (SENT_DATE)
FROM ACTIONQUEUE
WHERE SENT_DATE > 20.12.2022
GROUP BY USER_ID;

My result:

| User_ID | Count(Sent_date) |
| ------- | ---------------- |
| 1       | 1                |
| 2       | 2                |
| 5       | 2                |

Expected result:

| User_ID | Count(Sent_date) |
| ------- | ---------------- |
| 1       | 1                |
| 2       | 2                |
| 3       | 0                |
| 4       | 0                |
| 5       | 2                |

CodePudding user response：

Assuming the ACTIONQUEUE table already have all users which you want to report, then one solution here would be to count the condition in the current WHERE clause:

SELECT USER_ID, SUM(SENT_DATE > '2022-12-20') AS cnt
FROM ACTIONQUEUE
GROUP BY USER_ID;

CodePudding user response：

To expand the answer provided by @Tim Biegeleisen, your query is working only with records above 2022-12-20 so the records under that date won't exist in your query and won't be counted by count or be shown by the user_id

When you use what he suggested, the query will return a column for the user_ids and a column for the condition SENT_DATE > '2022-12-20'. If the condition is true it will return "1" and if it's false it will return "0" for each entry in the table ACTIONQUEUE. After that you can SUM the zeros and ones grouping by user_id.