I have a table that contains records of different types and values. I want to write a SQL query that can retrieve the type and value of each record, but only for the records that have the same date as today.
I also want to group the results by type, so that I can see how many records of each type I have for today. Additionally, I want to include all the types in the result, even if they have no records for today.
SELECT COUNT(id),
STATUS
FROM http_log as hl
WHERE CAST(hl.created_at AS DATE) = CAST(current_date AS DATE)
GROUP BY STATUS
It seems that you need to left join the distinct status values with your query so you can get the status groups with 0 rows:
WITH distinct_status as
(
SELECT DISTINCT status from http_log
)
SELECT COUNT(hl.id),
DS.status
FROM distinct_status AS DS LEFT JOIN http_log AS hl
ON DS.status = hl.status AND
CAST(hl.created_at AS DATE) = current_date
GROUP BY DS.status
For groups that have no match rows, the hl.id will be null and the count will be 0.