get row count according to the month in the date i

Question

I want a query in laravel to group by data with dateofSale's month with the count of rows with the same month.

here is my table

and I want to output something like this

numberOfSales | Month
     2           Nov
     1           Oct

how can I do it?

Answer 1

You should be able to achieve this what you're after with:

$data = \App\SaleData::selectRaw('COUNT(*) as count, YEAR(dateOfSale) year, MONTH(dateofSale) month')
    ->groupBy('year', 'month')
    ->get();

If you don't include the year as well then you'll have sales from different years included in the same month e.g. sales from October 2017 and October 2018 will be included together.

Answer 2

you can try as

$data = SalesData::select(DB::raw('count('*') as `count`'), DB::raw('MONTH(dateOfSale) month'))
         ->groupBy(function($m) {
             return Carbon::parse($m->dateOfSale)->format('m');
         })->get();

don't forget to import carbon and DB on top of your controller like

use DB;
use Carbon\Carbon;