Count visitors by month

Question

I have a question, so I want to build a chart compose of count visitors by date; I have in my database

id          created_at
1           2014-11-09
2           2014-11-10
3           2014-11-08
4           2014-10-01
5           2014-10-05

So I want to get

3 november
2 octomber

I want to build in Laravel, my code is:

$aVisitors = $oVisitor::select([
                            DB::raw('DATE(created_at) AS date'),
                            DB::raw('COUNT(id) AS count'),
                            ])
                            ->groupBy('date')
                            ->orderBy('date', 'ASC')
                            ->get();

Please help me. Thx in advance

Answer 1

use next

GROUP BY DATE_FORMAT(created_at, '%Y-%d')

or this

GROUP BY YEAR(created_at), MONTH(created_at)

in your laravel code it should be something like this

->groupBy('YEAR(created_at), MONTH(created_at)')

also you can get month name in directly from mysql

DB::raw("DATE_FORMAT(created_at, '%M') as monthName")

Answer 2

You can use here:

$aVisitors = $oVisitor::select([
    DB::raw("DATE_FORMAT(created_at, '%Y-%m') AS `date`"),
    DB::raw('COUNT(id) AS count'),
    ])
    ->groupBy('date')
    ->orderBy('date', 'ASC')
    ->get();

This will give you something like that:

2014-07 1
2014-08 1
2014-09 1
2014-10 5