BN83 BN83 - 29 days ago 8
MySQL Question

Select and count results group where date is today and this week

I currently have:

SELECT *, COUNT(*) AS counter
FROM Events
WHERE TO_DAYS(EventDate) - TO_DAYS(NOW()) >= 7;
ORDER BY EventDate


But what I'd like to do is count only upcoming results and to show how many are today and how many are in the current week, so not 7 days, but the actual Monday - Sunday working week. Is this possible with 1 query?

Answer
SELECT *, (
           SELECT COUNT( * ) FROM events WHERE WEEK( EventDate ) = WEEK( NOW() ) 
          ) as counter
FROM events
ORDER BY EventDate

Try this, hopefully it will select all the information and then as a sub-query count the total events for the week.