Be part of JetBrains PHPverse 2026 on June 9 – a free online event bringing PHP devs worldwide together.
In MySQL, I have a table with data like (the table's name is Persons):
Id (int), Name (varchar), Created_at (timestamp), Updated_at (timestamp).
1 Percy 2018-08-09 00-00-00 2018-08-09 00-00-00
2 Josh 2018-09-02 00-00-00 2018-09-02 00-00-00
3 Anna 2018-09-15 00-00-00 2018-09-15 00-00-00
4 Chris 2018-11-02 00-00-00 2018-11-02 00-00-00
I need an SQL to get the number of records created for a month for the past 5 months (including this month). If the month has no record, a 0 should be returned. Expected returned data with the data in the columns above would be:
month count
november 1
october 0
september 2
august 1
july 0
The months name are dynamic, meaning november is this months, october the last, etc. I need the data like this, as I'm going to use is in a bar chart, with the month as the x-axis, and the count as the y-axis.
I have tried:
SELECT COALESCE(MAX(EXTRACT(MONTH FROM created_at)), 0) AS Months, COUNT(*) AS Count
FROM persons
WHERE created_at BETWEEN CURDATE() AND CURDATE() - INTERVAL 5
GROUP BY EXTRACT(MONTH FROM created_at).
Could it be done easier with Eloquent?
In advance, thank you.
This partially solves your issue. With this you could then loop over an array of months and if that month exists in this collection you can return that number else return 0;
You could also take the code below and just merge it with an array of all the months that have a default value of 0.
$data = Person::all()->sortBy(function ($item) {
return -$item->created_at->month;
})->groupBy(function ($item) {
return $item->created_at->format("F");
})->map->count();
@realrandyallen thank you dear for your great support
@REALRANDYALLEN - Thanks, man. It was a great help!
Please or to participate in this conversation.