How can I count this when there are no rows?

Question

I am trying to count the number of days of the current week that do not have an event associated with them, but I don't know how to do it.

For example, I count the number of events happening this week with this query:

SELECT COUNT(e.event_id) FROM cali_events e
LEFT JOIN cali_dates d
ON e.event_id = d.event_id
WHERE YEARWEEK(d.date) = YEARWEEK(CURRENT_DATE())

But I only know how to count the days by counting each individual day and summing up the days that return 0, but that's not very elegant.

How could I do it with one query?

Answer 1

This might be a case for one of those 'value tables' where you create a table with possible days/weeks/whatever and then join for those that DON'T match & count them.

Answer 2

So you have a Dates table and an Events table?

Would an event_id only show up in the Dates table if there was an event?

If so, couldn't you just do an INNER JOIN?

Answer 3

This query uses a subquery to find all the unique dates in the joined tables. Then the number of unique dates is subtracted from seven?

--assuming a 7 day week; no mention in the request about workweek.
      SELECT 7 - COUNT(*) AS NumDaysWithoutEvents
        FROM    
        (SELECT DAY(d.date) 
            FROM cali_events e
            LEFT JOIN cali_dates d
            ON e.event_id = d.event_id
            WHERE YEARWEEK(d.date) = YEARWEEK(CURRENT_DATE())
            GROUP BY DAY(d.date)
        ) AS UniqueDates

Answer 4

Couldn't you write you query to find the distinct dates on which there is an event during the time frame that you're interested in?

Answer 5

I think this will work but I also think there's a solution that uses HAVING.

SELECT COUNT(*) FROM
    (SELECT d.date, count(*) as event_count
    FROM cali_dates d
        LEFT JOIN cali_events e ON d.event_id = e.event_id
    WHERE YEARWEEK(d.date) = YEARWEEK(CURRENT_DATE())
    GROUP BY d.date) t
WHERE t.event_count = 0

Edited to add: this assumes no time portion in d.date, although it would be easy to accommodate that. It also assumes that cali_dates contains a record for every day of the week.

Answer 6

Flip the query around and select from cali_dates if no records exist in cali_events for an event_id:

select count(*)
  from cali_dates d
 where not exists( select *
                     from cali_events e
                    where d.event_id = e.event_id )
   and YEARWEEK(d.date) = YEARWEEK(CURRENT_DATE())

Answer 7

Assuming your Cali_Dates table has a row for every day in the week:

From the Cali_Dates table, where it's the current week, find all the rows that have nulls (no events) in the Cali_Events table.

SELECT sum(case when e.event_id is null then 1 else 0 end)
FROM cali_dates d
LEFT JOIN cali_events e
ON e.event_id = d.event_id
WHERE YEARWEEK(d.date) = YEARWEEK(CURRENT_DATE())
GROUP BY d.date

Otherwise, you'd have to declare the number of days in the week, like in pcambell's answer:

Edit: forgot to add the group by