We help IT Professionals succeed at work.

How to construct a MySQL query to get number of entries by month.

Lennart Ericson
on
342 Views
Last Modified: 2014-05-22
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
Comment
Watch Question

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.

Author

Commented:
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?
This one is on us!
(Get your first solution completely free - no credit card required)
UNLOCK SOLUTION

Author

Commented:
Thanks a lot!
That change made it! I ought to have understood.
Unlock the solution to this question.
Join our community and discover your potential

Experts Exchange is the only place where you can interact directly with leading experts in the technology field. Become a member today and access the collective knowledge of thousands of technology experts.

*This site is protected by reCAPTCHA and the Google Privacy Policy and Terms of Service apply.

OR

Please enter a first name

Please enter a last name

8+ characters (letters, numbers, and a symbol)

By clicking, you agree to the Terms of Use and Privacy Policy.