'Conditionally COUNT the Value of a Column in SQL and Account for NULL Value
I have a SQL query where I try to perform some conditions inside a COUNT(). I am using PostgreSQL. Here is my original query:
SELECT s.schedule_ID as "schedule_ID", schedule_start, schedule_end, spots,
COUNT(CASE a.schedule_ID WHEN s.schedule_ID THEN TRUE ELSE NULL END) as "takenSpots"
FROM schedules s, appointments a
GROUP BY s.schedule_ID
ORDER BY s.schedule_ID;
Basically, this counts the schedule_ID from appointments only when it matches the schedule_ID in schedules table. The issue is that this query does not account for the case when a.schedule_ID is null from appointments table. When it is null, it returns no records.I want to account for null values and if it is null, then return 0.
After some research I found that something like this must work:
SELECT s.schedule_ID as "schedule_ID", schedule_start, schedule_end, spots,
COUNT(CASE a.schedule_ID WHEN s.schedule_ID THEN TRUE WHEN NULL THEN '0' END) as "takenSpots"
FROM schedules s, appointments a
GROUP BY s.schedule_ID
ORDER BY s.schedule_ID;
But no luck and still no record is returned here even when I check for NULL value. Can someone please help me out?
I appreciate your help!
Sources
This article follows the attribution requirements of Stack Overflow and is licensed under CC BY-SA 3.0.
Source: Stack Overflow
| Solution | Source |
|---|
