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

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

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

Lennart Ericson

ASKER

Thanks a lot!
That change made it! I ought to have understood.