How to limit results by SUM

Posted by superspace on Stack Overflow See other posts from Stack Overflow or by superspace
Published on 2010-03-14T10:39:34Z Indexed on 2010/03/14 10:45 UTC
Read the original article Hit count: 154

Filed under:
|
|
|
|

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

© Stack Overflow or respective owner

Related posts about mysql

Related posts about sql