asked on How to construct a MySQL query to get number of entries by month.
In a MySQL database table there are dates (Y-m-d). I'd like to count the number of entries by month (like jan 2014. feb 2014, mar 2104, etc)
I have tried several ways, among them
$SQL4 = "SELECT * FROM pagecounter WHERE 'date >= $min_date' AND 'date <= $max_date' GROUP BY MONTH(date)";
How should I construct the the Query? I can't get it to work. Appreciate help.
test-1.php
I have tried several ways, among them
$SQL4 = "SELECT * FROM pagecounter WHERE 'date >= $min_date' AND 'date <= $max_date' GROUP BY MONTH(date)";
How should I construct the the Query? I can't get it to work. Appreciate help.
test-1.php
MySQL Server
Last Comment
Lennart Ericson
ASKER
Thanks for your reply.
I am not sure I understand, Please explain Field1, field2 and MyCount.
I tried
$SQL4 = "SELECT *, count(*), min(date) as MyCount FROM pagecounter WHERE date BETWEEN '$min_date' and '$max_date' Group by Year(date), Month(date)";
$res4 = $mysqli->query($SQL4);
if (!$res4)
{ $err = "QUERY FAIL: ".$SQL4. ' ERRNO: '. $mysqli->errno.' ERROR: '.$mysqli->error; trigger_error($err, E_USER_ERROR); }
while ($row = $res4->fetch_array())
{
$mycount = $row['MyCount'];
and $mycount printed "2014-05-01" only. What did I do wrong?
I am not sure I understand, Please explain Field1, field2 and MyCount.
I tried
$SQL4 = "SELECT *, count(*), min(date) as MyCount FROM pagecounter WHERE date BETWEEN '$min_date' and '$max_date' Group by Year(date), Month(date)";
$res4 = $mysqli->query($SQL4);
if (!$res4)
{ $err = "QUERY FAIL: ".$SQL4. ' ERRNO: '. $mysqli->errno.' ERROR: '.$mysqli->error; trigger_error($err, E_USER_ERROR); }
while ($row = $res4->fetch_array())
{
$mycount = $row['MyCount'];
and $mycount printed "2014-05-01" only. What did I do wrong?
ASKER CERTIFIED SOLUTION
THIS SOLUTION ONLY AVAILABLE TO MEMBERS.
View this solution by signing up for a free trial.
Members can start a 7-Day free trial and enjoy unlimited access to the platform.
ASKER
Thanks a lot!
That change made it! I ought to have understood.
That change made it! I ought to have understood.
$SQL4 = "Select Field1, Field2, count(*), min(date) as MyCount from pagecounter Where date between '$min_date' and '$max_date' Group by Year(date), Month(date)";
I'm grouping by both year and month so that Jan 2013 and Jan 2014 don't get put in the same group. I'm also selecting the first date from each of those groups so you can identify the group.