Avatar of Lennart Ericson
Lennart Ericson
Flag for Sweden 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
MySQL Server

Avatar of undefined
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?
ASKER CERTIFIED SOLUTION
Daniel Wilson

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.
See Pricing Options
Start Free Trial
GET A PERSONALIZED SOLUTION
Ask your own question & get feedback from real experts
Find out why thousands trust the EE community with their toughest problems.
Lennart Ericson

ASKER
Thanks a lot!
That change made it! I ought to have understood.
Experts Exchange has (a) saved my job multiple times, (b) saved me hours, days, and even weeks of work, and often (c) makes me look like a superhero! This place is MAGIC!
Walt Forbes