Q:

Laravel: Get count of total number of pass and fail results from mysql

i'm stuck in this problem. i have a table in database which os_flash_result which can be either PASS or FAIl.
i have to display the number of fail and pass result in the form of graph with date.
My table structure and data is as follows:
mysql> select * from mobile_prod_pograms;
+----+-----------+-----------------+------------------------+---------+----------+---------------------+---------------------+
| id | device_id | os_flash_result | action                 | station | build_id | created_at          | updated_at          |
+----+-----------+-----------------+------------------------+---------+----------+---------------------+---------------------+
|  2 | 1         | FAIL            | Mobile Production Prog |       1 | mp1      | 2018-09-27 00:00:00 | 2018-09-27 00:00:00 |
| 19 | 1         | PASS            | Mobile Production Prog |       1 | mp1      | 2018-09-27 00:00:00 | 2018-09-27 00:00:00 |
| 20 | 1         | PASS            | Mobile Production Prog |       1 | mp1      | 2018-09-27 00:00:00 | 2018-09-27 00:00:00 |
+----+-----------+-----------------+------------------------+---------+----------+---------------------+---------------------+
3 rows in set (0.00 sec)

i'm using laravel so i have wirtten the following query to get Count of PASS results.
$mobile_chart_query =  DB::table('mobile_prod_pograms')
            ->select(DB::raw('DATE(created_at) as date'), DB::raw('count(id) as total'))
            ->where('os_flash_result','=','PASS')
            ->where('created_at','>=','2018-09-17')
            ->groupBy('date')
            ->get();

The above Query is working fine.
but i want to get failure and pass results count in a single query with date.How can i achieve that?
Any help will be Appreciated...

A:

You may use conditional aggregation:
$mobile_chart_query =  DB::table("mobile_prod_pograms")
    ->select(DB::raw("DATE(created_at) AS date"),
             DB::raw("COUNT(CASE WHEN os_flash_result = 'PASS' THEN 1 END) AS pass_count"),
             DB::raw("COUNT(CASE WHEN os_flash_result = 'FAIL' THEN 1 END) AS fail_count"))
    ->where("created_at", ">=", "2018-09-17")
    ->groupBy("date")
    ->get();

This corresponds to the following raw MySQL query:
SELECT
    DATE(created_at) AS date,
    COUNT(CASE WHEN os_flash_result = 'PASS' THEN 1 END) AS pass_count,
    COUNT(CASE WHEN os_flash_result = 'FAIL' THEN 1 END) AS fail_count
FROM mobile_prod_pograms    -- should be called mobile_prod_programs
WHERE
    created_at >= '2018-09-17'
GROUP BY
    date;

