On Tue, Mar 16, 2004 at 02:35:57PM +0200, Tommi Virtanen wrote:
> How I can calculate following:
>
> I have table:
> id(int) start_date(date) end_date(date)
> 001 2004-03-10 2004-03-12
> 002 2004-03-27 2004-04-02
> 003 2004-04-05 2004-04-06
>
> Total count of date is 12. But how I can calculate count of
> date per month?
>
> regards,
I'm assuming this is in a MySQL table. Is that correct? This is
probably something that will better be answered on a MySQL list.
Assuming that it is MySQL, how are you deciding which month an item is
in? What if it starts in March and ends in April? Should it be
considered to be in March or April?
MySQL's COUNT[1], GROUP BY[2], and DATE_FORMAT[3] will be helpful to you.
Something like:
SELECT COUNT(*) AS num, DATE_FORMAT(end_date, "%Y-%m") AS yearmonth
FROM yourtable
GROUP BY yearmonth
ORDER BY num;
might work[4].
[1] http://www.mysql.com/doc/en/GROUP-BY-Functions.html#IDX1452
[2] http://www.mysql.com/doc/en/SELECT.html
[3] http://www.mysql.com/doc/en/Date_and_time_functions.html#IDX1341
[4] This query assumes that end_date is sufficient to determine which
month something is "in". Also, it relies on some potentially
MySQL-specific syntax.
joel
--
[ joel boonstra | gospelcom.net ]
--
PHP General Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php
