Get Data Grouped By Dates Even the date when have 0 records

hi, i have orders table and iwant to get the data grouped by date even the dates where there is no records this is my code

return DB::table('orders')
            ->select([DB::raw("DATE_FORMAT(created_at, '%d-%m-%Y') as date"), DB::raw('count(*) as orders'), DB::raw('sum(total_price) as sale_volume')])
            ->orderBy('created_at')
            ->groupBy(DB::raw("DATE_FORMAT(created_at, '%d-%m-%Y')"))
            ->get();

this will give me this data

[
{
"date": "31-10-2022",
"orders": 7,
"sale_volume": 34650
},
{
"date": "01-11-2022",
"orders": 3,
"sale_volume": 14400
},
{
"date": "04-11-2022",
"orders": 2,
"sale_volume": 9700
},
{
"date": "05-11-2022",
"orders": 6,
"sale_volume": 29600
},
{
"date": "06-11-2022",
"orders": 2,
"sale_volume": 10300
}
]

what i want is the data to not miss the days where there is no orders like 2-11-2022 and 3-11-2022 want it to show orders 0 and sale_volume 0

Tray2

3 years ago

Level 73

If it's not in the database then you will not have it in the result.

To acheive that you need to pull the dates from another table and then show results from that table if it does not exist in the first table. It's quite tricky to acheive that.

Something like this. (Not tested)

SELECT d.date,  
			(SELECT COUNT(*) 
			  FROM orders o 
			  WHERE o.created_at = d.date) ,
			 (SELECT SUM(total_price)
			   FROM orders o2 
				WHERE o2.created_at = d.date) 
 FROM dates;

Please or to participate in this conversation.