Re: Calculate

joel boonstra <jboonstra@gospelcom.net> wrote: > > 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 > >