Laravel query builder; get distinct rows with sums

Question

Say we have a SQL table named BoxContents with each row consisting of id, boxID, itemID, and quantity.

Only unique value is id. I need to input boxID and get a list/array of itemIDs and their TOTAL quantity;

Example: in BoxContents table:

id	boxID	itemID	quantity
1	foo	banana	5
2	foo	monkey	1
3	bar	bomb	2
4	foo	banana	5
5	bar	fuse	2
6	bar	banana	5
7	foo	banana	5

result when querying box foo:

['banana'=>15, 'monkey'=>1]

result when querying box bar:

['bomb'=>2, 'fuse'=>2, 'banana'=>5]

How would I go about making a query?

DB::table('BoxContents')
    ->where('boxID', 'foo')
    ->select('itemID', SUM('quantity'))
    ->distinct();

is what I got so far, but SUM() is obviously not accepted in select()

Can this be done?

user_7451109 · Answer

You can do this via selectRaw()/DB::raw():

DB::table('BoxContents')
    ->where('boxID', 'foo')
    ->select('itemID', DB::raw("SUM('quantity')"))
    ->distinct();