How to limit results by SUM
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 |
+------------+------------+
- use a date function to limit the 14 day range of date
- 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
I think that using LIMIT 0, 10
will work for you.
精彩评论