How to use query builder with sum() column and groupBy

Question

How would I use query builder in Laravel to generate the following SQL statement:

SELECT costType, sum(amountCost) AS amountCost
FROM `itemcosts`
WHERE itemid=2
GROUP BY costType

I have tried several things, but I can't get the sum() column to work with a rename.

My latest code:

$query = \DB::table('itemcosts');
$query->select(array('itemcosts.costType'));
$query->sum('itemcosts.amountCost');
$query->where('itemcosts.itemid', $id);
$query->groupBy('itemcosts.costType');
return $query->get();

Answer 1

Using groupBy and aggregate function (sum / count etc) doesn't make sense.

Query Builder's aggregates return single result, always.

That said, you want raw select for this:

return \DB::table('itemcosts')
    ->selectRaw('costType, sum(amountCost) as sum')
    ->where('itemid', $id)
    ->groupBy('costType')
    ->lists('sum', 'costType');

Using lists instead of get is more appropriate here, it will return array like this:

[
 'costType1' => 'sumForCostType1',
 'costType2' => 'sumForCostType2',
 ...
]

With get you would have:

[
 stdObject => {
   $costType => 'type1',
   $sum => 'value1'
 },
 ...
]