I have been wrestling with a SQL issue for the last few hours. I have a database that keeps track of how many products are built of each color available. I want to have a table that displays the color name and the number of products built in that color. It would look like this:
ART 1
CAL 6
CSP 10
DIA 12
MAY 8
SAN 1
The problem I am having is that if any of the colors are zero, it does not display the color name or the zero. It just leaves that row off. I have to be able to show it was zero. The value in the database is zero (0), not NULL, just for clarification purposes.
Here is the query I am using:
SELECT color, COUNT(color)
FROM production_schedule_yearly
WHERE (date BETWEEN '9/2/2024' AND '9/8/2024')
GROUP BY color ORDER BY color
If anyone can explain to me how to include the rows where the value is zero, I would be most grateful. I have been searching and every solution seems to involve two tables, but I only have one.
I haven’t really tried any solutions I have run across because they involve having a second table.
3
Presumably the colors you want are mentioned somewhere in that production_schedule_yearly table, even if not within the date range you want to view.
So, you can do something like this to get a list of all the possible colors.
SELECT colors.color, COALESCE(COUNT(sch.color), 0) numb
FROM ( SELECT DISTINCT color FROM production_schedule_yearly ) colors
LEFT JOIN production_schedule_yearly sch
ON colors.color = sch.color
AND sch.date BETWEEN '9/2/2024' AND '9/8/2024'
GROUP BY colors.color
ORDER BY colors.color
The COALESCE operator makes your result set contain zeros where it otherwise would have contained NULLs.
I don’t have your data so this isn’t debugged.
You can left join and group, like this:
SELECT a.color, COALESCE(COUNT(b.color), 0)
FROM production_schedule_yearly a
LEFT JOIN production_schedule_yearly b
ON a.color = b.color AND (b.date BETWEEN '9/2/2024' AND '9/8/2024')
GROUP BY a.color
ORDER BY a.color
The reason you did not see the zero you expected was that there was no record for those colors whose date was to fulfill your criteria. So, you need to find all colors, this is why we group by a.color and we left join with the same table, aliased as b. A LEFT JOIN will have all records at the left-hand side (here aggregated by color) and the right-hand-side is counted and coalesced to 0 if the count would ever result in null.
Another approach, skip the join and instead use case expression to do conditional aggregation:
SELECT color,
SUM(case when date BETWEEN '9/2/2024' AND '9/8/2024' then 1 else 0 end)
FROM production_schedule_yearly
GROUP BY color ORDER BY color