How to limit results by SUM
- by superspace
I have a table of events called event. For the purpose of this question it only has one field called date.
The following query returns me a number of events that are happening on each date for the next 14 days:
SELECT
DATE_FORMAT( ev.date, '%Y-%m-%d' ) as short_date,
count(*) as date_count
FROM event ev
WHERE ev.date >= NOW()
GROUP BY short_date
ORDER BY ev.start_date ASC
LIMIT 14
The result could be as follows:
+------------+------------+
| short_date | date_count |
+------------+------------+
| 2010-03-14 | 1 |
| 2010-03-15 | 2 |
| 2010-03-16 | 9 |
| 2010-03-17 | 8 |
| 2010-03-18 | 11 |
| 2010-03-19 | 14 |
| 2010-03-20 | 13 |
| 2010-03-21 | 7 |
| 2010-03-22 | 2 |
| 2010-03-23 | 3 |
| 2010-03-24 | 3 |
| 2010-03-25 | 6 |
| 2010-03-26 | 23 |
| 2010-03-27 | 14 |
+------------+------------+
14 rows in set (0.06 sec)
Let's say I want to dislay these events by date. At the same time I only want to display a maximum of 10 at a time. How would I do this?
Somehow I need to limit this result by the SUM of the date_count field but I do not know how.
Anybody run into this problem before?
Any help would be appreciated. Thanks
