How to limit results by SUM

Question

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

Edited: The extra requirement (crucial one, oops) which I forgot in my original post, is that I only want whole days. ie. Given the limit is 10, it would only return the following rows:

+------------+------------+
| short_date | date_count |
+------------+------------+
| 2010-03-14 |          1 | 
| 2010-03-15 |          2 | 
| 2010-03-16 |          9 | 
+------------+------------+

Answer 1

I think that using LIMIT 0, 10 will work for you.

Answer 2

1. use a date function to limit the 14 day range of date
2. use limit to display the first 10

---

SELECT 
   DATE_FORMAT( ev.date, '%Y-%m-%d' ) as short_date,
   count(*) as date_count
FROM event ev
WHERE ev.date between NOW() and date_add(now(), interval 14 day)
GROUP BY date(short_date)
ORDER BY ev.start_date ASC
LIMIT 0,10