Date Count

NZ_Kiwis

Hey... I think this is real simple but how do you write i code which will count all the rows in a table and display them all in a total..

ie: i have a "date" field in my MySQL table.. i want it to count all rows with "2005-05-05" then "2005-05-06" and so on and show them in total figures..

any ideas??

drew010

the best i can come up with is

SELECT SUM(acolumn) FROM table WHERE datefield = '2005-05-05'

that way you have to issue an individual query with each specific date in it. but its the best i can come up with.

kburger

I think this should do it.

SELECT datefield, COUNT(datefield) AS count FROM table GROUP BY datefield ASC;

The first field returned will be the date and the second field will be the number having that date, all sorted by date.

NZ_Kiwis

Originally posted by kburger
I think this should do it.

SELECT datefield, COUNT(datefield) AS count FROM table GROUP BY datefield ASC;

The first field returned will be the date and the second field will be the number having that date, all sorted by date.

OKay.... I

echo $datefield;

but how do echo the total count???

abc

each row return from the query will contain the sum and the date for that total!

$r = mysql_query ( $sql );

while ( $row = mysql_fetch_assoc ( $r ) )
{
echo $row['datefield']; // the 'datefield' column value
echo $row['count']; // total rows having the value found in column 'datefield'
}

abc