Getting count as 0 if no data found

I have 2 tables, client table and online_hd table

client table

company_code  |  branch_code  |     des3
===============================================
	2 					1		     tawain
	2					2		     Berma
	2					3		     Godha

online_hd table

company_code 	| branch_code    |   slip_no
===========================================
	2					1				1
	2			        1				2
	2			        1				3

My need is to get the count of the slip_no groupBy branch_code

$branchwise = Client::leftJoin('online_hd','client.branch_code','=','online_hd.branch_code')
                                ->where('online_hd.company_code',2)
                                ->where('client.company_code',2)
                                ->selectRaw('count(online_hd.slip_no) as total_sales,client.des3')
                                ->groupBy('online_hd.branch_code')
                                ->groupBy('client.des3')
                                ->get();

The result I need is

tawain : 3
Berma : 0
Godha : 0

But I am only getting the result of values in the online_hd table like

tawain : 3

How to get the count as 0, if no data is available in the online_hd for that branch_code

Tray2

4 years ago

Level 73

Something like this should work

SELECT c.des3, 
	(SELECT COUNT(oh.*) 
     FROM online_hd oh
     WHERE oh.branch_code = c.branch_code) total_sales
FROM clients c

1 like

Please or to participate in this conversation.