[RESOLVED] Sum ?

MichaelHe

I have some problems counting some data.

I have some data put into a database by a webcounter.
In the database I have a date and a counter field like this.

Date 23-10-2006 - Count 14
Date 22-10-2006 - Count 11
and so on.

I have found the date with the highest number of hits like this:

$gethighest = mysql_query("SELECT * FROM CookieCounter ORDER BY Count DESC LIMIT 1");
while ($row = mysql_fetch_array($gethighest)){$highest=$row['Count'];}

This just finds the date with the highest number in the field Count.

But I need to find the Month with the highest number of hits, but I can't get it to work.
I don't need to know which month has the highest, but I only need to know the number of hits in that month.
So somehow I need it to sum the Count field for each month, and show me the sum number of hits from the best month.

I'm a little lost, so I need a little help :o)

bastien

select count(posts) from table group by date_format(date_field,'%m')

Piranha

Adduming that bastien have the correct way to solve the data issue it should be something like this:

SELECT SUM(count) as sum
FROM table
HAVING MAX(SUM(count))
GROUP BY date_format(date_field,'%m')

If this works I think the above is the best. But I'm not 100% sure that the HAVING will work, if not you can try this:

SELECT SUM(count) as sum
FROM table
GROUP BY date_format(date_field,'%m')
ORDER BY sum DESC
LIMIT 0, 1

MichaelHe

Thanks a lot...
I got it to work by using some of the ideas :o)