PHPize Online / SQLize Online  /  SQLtest Online

A A A
Share      Blog   Popular
Copy Format Clear
CREATE TABLE applications ( id int, status_id int, is_active enum('true','false') ); INSERT INTO applications (id, status_id, is_active) VALUES (1, 1, 'true'), (2, 1, 'true'), (3, 1, 'true'), (4, 2, 'true'), (5, 2, 'true'), (6, 1, 'false'), (7, 3, 'false'); CREATE TABLE status ( id int, `desc` varchar(10) ); INSERT INTO status (id, `desc`) VALUES (1, 'completed'), (2, 'pending'), (3, 'processed'), (4, 'failed');
Copy Clear
Copy Format Clear
<?php var_export( $db->table('status') ->selectRaw("COALESCE(`desc`, 'total') `desc`, COALESCE(SUM(is_active = 'true'),0) count") ->leftJoin('applications', 'status.id', '=', 'applications.status_id') ->groupByRaw('`desc` WITH ROLLUP') ->orderBy('desc', 'DESC') ->get() ->pluck('count', 'desc') ->sort() //->pipe(fn($coll) => $coll->put('total', $coll->sum())) ->toArray() /*table('stores') ->selectRaw('SUM(store_id = 1) one') ->selectRaw('SUM(store_id = 2) two') ->selectRaw('SUM(store_id = 3) three') ->selectRaw('COUNT(1) total') ->selectRaw("SUM(country = 'Netherlands') dutch") ->selectRaw("SUM(country = 'Sweden') swedish") ->first()*/ );
Show:  
Copy Clear