I have the following query and been fighting with from yesterday after solving previous issue with the help of great stack-overflowers yesterday's question.
$sales = DB::table('sales')
->leftJoin('store_configs', 'store_configs.id', '=', 'sales.store_config_id')
->leftJoin('category_sales', 'category_sales.sale_id', '=', 'sales.id')
->leftJoin('categories', 'categories.id', '=', 'category_sales.category_id')
->leftJoin('departments', 'departments.category_id', '=', 'categories.id')
->leftJoin('department_sales', 'department_sales.sale_id', '=', 'sales.id')
->select('sales.date',
DB::raw('sales.id as sales_id'),
DB::raw('(IFNULL(sum(department_sales.amount), 0)) as department_sales'),
DB::raw('(IFNULL(sum(category_sales.amount), 0)) as cat_total')
)
->groupBy('date', 'sales.id')->orderBy('date', 'desc')->get();
The following is the result output from above query and it is wrong:
I have a table structure with following data: categories_sales has following data:
department_sales
As per the table : I expected result should have been :
department_sales: 10 for sale_id = 1
department_sales: 5 for sale_id = 2
category_sales: 200 for sale_id 1
category_sales: 30 for sale_id 2
Can someone please give me some idea ? I will be really really thankful.
