Laravel model query with sum and grouping

Question

I have a table like this:

|item_name  |uom    |qty    |order_number

|bike       |pc     |1      |101
|ball       |pc     |2      |102
|bike       |pc     |5      |103
|car        |pc     |3      |106
|bike       |pc     |4      |108
|ball       |pc     |1      |109
|bike       |pc     |6      |115
|car        |pc     |1      |111

And I want to see in a view this data:

bike - 16 pcs overall:
101 - 1
103 - 5
108 - 4
115 - 6

ball - 3 pcs overall:
102 - 2
109 - 1

etc... I tried wuth this query, but I think it is not correct, because it gives me only name and sum of qty:

$saps = Item::where('year', 2019)->groupBy('item_name')->selectRaw('item_name, sum(qty) as sum')->get('item_name','sum');

Because you are selecting only item_name and sum, that why you are getting only those two values. — Prashant Deshmukh.....
– Prashant Deshmukh....., Commented Dec 27, 2019 at 11:07
This is how the SQL group by works so in that sense it's correct. Maybe you need to use the collection group by method instead to get something closer to what you want — apokryfos
– apokryfos, Commented Dec 27, 2019 at 11:20

Golam Sorwar · Accepted Answer · 2019-12-27 19:42:18Z

1

Item::where('year', 2019)
    ->select('name','order_number',\DB::raw("SUM(qty) as qty"))
    ->groupBy('name')
    ->havingRaw('COUNT(*) > 1')
    ->get();

Try this query.

Outupt

answered Dec 27, 2019 at 19:42

Golam Sorwar

1,67517 silver badges12 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Prashant Deshmukh..... · Accepted Answer · 2019-12-27 11:27:36Z

0

Try this query.

$saps = Item::where('year', 2019)->selectRaw('order_number, qty')
                    ->groupBy('item_name')
                    ->get();

Or

$saps = Item::where('year', 2019)->selectRaw('order_number, qty,item_name')
                    ->get();

$saps_new = $saps->groupBy('item_name')->map(function ($row) {
                    return $row->sum('qty');
            });

edited Dec 27, 2019 at 11:27

answered Dec 27, 2019 at 11:13

Prashant Deshmukh.....

2,2921 gold badge11 silver badges11 bronze badges

2 Comments

Yasin Patel Over a year ago

It will not work. it returns sum of qty with first order number of item name

Prashant Deshmukh..... Over a year ago

@YasinPatel I guess it will work now, but it wont get exact result as wanted.

shahburhan · Accepted Answer · 2019-12-27 11:40:26Z

0

You can get the initial groups with this:

$saps = Item::where('year', 2019)->selectRaw("SUM(qty) as total, item_name, id")
                ->groupBy('item_name')
                ->get();

You can then lazy or eager load the listing for each item.

If you go with lazy loading you can map on results and then lazy load the listing based on each item

$saps->map(function($item){
     $items = Item::where('year', 2019)->where('item_name', $item->item_name)
                ->get();
}

or eager load like this:

Item::where('year', 2019)->whereIn('item_name', $saps)
            ->get();

answered Dec 27, 2019 at 11:40

shahburhan

2241 silver badge12 bronze badges

Collectives™ on Stack Overflow

Laravel model query with sum and grouping

3 Answers 3

Comments

2 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

Comments

2 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related