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
MySQL Server
Last Comment
Lennart Ericson
8/22/2022 - Mon
Daniel Wilson
You need to list the fields and include a COUNT.
$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.
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?
$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.