[Resolved] SQL Question

Pilly

My query is this:

SELECT DATE_FORMAT(datestamp,'%H') AS mytimestring , count(*) AS cnt
FROM phplog
GROUP BY mytimestring

What im trying to get is an hourly count. So for all records that have an datestamp of 11 it counts them

eg.

Hour Count
13 2
14 1
15 3

etc.

Can this be done? whats wrong with the query above?

Pilly

$sql = "SELECT , count() AS cnt , DATE_FORMAT(datestamp,'%H') AS mydatestring
FROM phplog
GROUP BY mydatestring
ORDER BY DATE_FORMAT(datestamp,'%H')
";

AstroTeg

Without seeing the table, I'd say this is your problem: count(*)

You will want to have count() keep track of a particular field - not all fields (because all fields will include the date field which is probably pretty unique). I'd take a guess you'd want something like this:

SELECT count(DATE_FORMAT(datestamp,'%H')) AS cnt
FROM phplog
GROUP BY DATE_FORMAT(datestamp,'%H')

I haven't tested this so I'm not 100% that will work.