John Davidson

Message:

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();  

i want at my row table :

total_transaksi     

10



transaksi_success    

4



transaksi_failed 



4    



transakisi_pending



2                    

---

My Answer:
You can achieve this by using conditional aggregation in your query. Here's an updated version of your query that includes the count of successful, pending, and failed transactions:

php
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'),
DB::raw('SUM(CASE WHEN a.status = "success" THEN 1 ELSE 0 END) as transaksi_success'),
DB::raw('SUM(CASE WHEN a.status = "failed" THEN 1 ELSE 0 END) as transaksi_failed'),
DB::raw('SUM(CASE WHEN a.status = "pending" THEN 1 ELSE 0 END) as transaksi_pending')
)
->groupBy(
'branch_id',
'customer_name',
'branch_name',
'branch_code',
'status',
'company_name',
'project_name',
'region_name',
)
->get();


This query uses `SUM` with `CASE` statements to count the number of successful, pending, and failed transactions based on the `status` column in your `covis_transactions` table. The results will be included in the `transaksi_success`, `transaksi_failed`, and `transaksi_pending` columns in your result set.