I have a problem in the query builder like the following...

See more:

SQL

PHP

Laravel

how do I distinguish the status of a transaction, if in my code now it only shows the total transaction, but not the status of each transaction, for example there are 10 transactions, now in 10 there are successful transactions 4 failed 4 cancel 4

What I have tried:

DB::table('covis_transactions as a')
->leftJoin('covis_customers as b', 'a.covis_customer_id','b.id')
->leftJoin('companies as c', 'b.company_id', 'c.id')
->leftJoin('projects as d', 'b.project_id', 'd.id')
->leftJoin('branchs as e', 'b.branch_id', 'e.id')
->leftJoin('regions as f', 'b.region_id', 'f.id')
->whereBetween('a.visited_at', [$from, $to])
->where('e.is_active', 1)
->select(
'a.status as status',
'b.name as customer_name',
'e.name as branch_name',
'c.name as company_name',
'd.name as project_name',
'f.name as region_name',
'e.code as branch_code',
'e.id as branch_id',
DB::raw('COUNT(a.id) as total_transaksi'),
)
->groupBy(
'branch_id',
'customer_name',
'branch_name',
'branch_code',
'status',
'company_name',
'project_name',
'region_name',
)
->get();