I'm having problems getting the SUM of a certain field from two tables.
So to start things off, here's my query.
//Getting of Cost of Goods Sold (Menus)
$totalMenuCost = DB::raw('(SUM(orders.qty * orders.cost)) as cost');
$yearMenuSold = DB::raw('YEAR(orders.created_at) as year');
$menuscost = DB::table('orders')
->where('status', 'served')
->select($totalMenuCost, $yearMenuSold);
//Getting of Cost of Goods Sold (Items)
$totalItemCost = DB::raw('(SUM(purchases.qty * purchases.cost)) as cost');
$yearItemSold = DB::raw('YEAR(purchases.created_at) as year');
$itemcost = DB::table('purchases')
->where('status', 'served')
->union($menuscost)
->select($totalItemCost, $yearItemSold)
->get();
And when I try to do return $itemcost
. It returns two rows:
[
{
cost: "792.00",
year: 2017
},
{
cost: "1700.00",
year: 2017
}
]
I'm trying to make it return a single row but having it added, like this:
[
{
cost: "2492.00", // that's 1,700 + 792
year: 2017
}
]