CODEWITHSUNDEEP
laraveleloquent
Pasindu
Pasindu

Reputation: 340

get row count according to the month in the date in Laravel

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 enter image description here

and I want to output something like this

numberOfSales | Month
     2           Nov
     1           Oct

how can I do it?

Upvotes: 1

Views: 4473

Answers (4)

kakaroto codes
kakaroto codes

Reputation: 79

I strongly recommend you to use standardized timestamps in your 'dateofSale' field. This way you have access to more native solutions to your application. In this case, the answer to this problem is simply:

ModelName::query()
->whereMonth('created_at', '9')
->count();

Upvotes: 0

Nigus Abate
Nigus Abate

Reputation: 11

Don't forget to import your Models in your controller. just like this enter image description here

Upvotes: 0

Rwd
Rwd

Reputation: 35200

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.

Upvotes: 2

Usman Jdn
Usman Jdn

Reputation: 827

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;

Upvotes: 0

Related Questions