SQL grouping with 3 data-CodePudding

I think what I need isn't that complex but I've already spent days playing with SQL queries.

Here my basic table structure

id |  status  | date
1  |  active  | 2020-01-02
2  | complete | 2020-01-03
3  | complete | 2020-01-03
4  |  active  | 2020-01-03

I'm trying to achieve this result on my query, grouping the result by date then counting the following

total - based on total row count by date, 
active - based on active status by date, 
complete - based on complete status by date

this is my desired format below

[
    {
        total: 1,
        active: 1,
        completed: 0,
        date: "2020-01-02"
    },
    {
        total: 3,
        active: 1,
        completed: 2,
        date: "2020-01-03"
    }
]

This runs on Laravel and I'm trying to play with Eloquent as well as the Query builder but no success

$leadReport = Lead::select(
    DB::raw('count(id) as `total`'),
    //DB::raw('count(CASE WHEN `status` = `active ) as `active`'),
    //DB::raw('count(CASE WHEN `status` = `complete`) as `completed`'),
    DB::raw("DATE_FORMAT(created_at, '%Y-%m-%d') as date")
)->where('iso','au')->groupBy('date')->orderBy('date')->get();

return $leadReport;

Appreciate any help

EDIT just want to thank you @Yazan for the recommended query by using SUM, I manage to use it on laravel query builder like below

$leadSummary = Lead::select(
    DB::raw("Sum(CASE WHEN status = 'completed' AND json_unquote(json_extract(`lenders`, '$.current_status')) IN ('Settled', 'Funded') THEN 1 ELSE 0 END) AS settled"),
    DB::raw("Sum(CASE WHEN status = 'completed' AND json_unquote(json_extract(`lenders`, '$.current_status')) NOT IN ('Settled', 'Funded') THEN 1 ELSE 0 END) AS rejected"),
    DB::raw("Sum(CASE WHEN status = 'active'THEN 1 ELSE 0 END) AS active ")
)->where('iso','au')->get();

CodePudding user response：

use sum instead of count like this

SELECT Count(id)                     AS total,
       Sum(CASE
             WHEN status = 'active' THEN 1
             ELSE 0
           END)                      AS active,
       Sum(CASE
             WHEN status = 'completed' THEN 1
             ELSE 0
           END)                      AS completed,
       Date_format(date, '%Y-%m-%d') AS date
FROM   MYtable
GROUP  BY date
ORDER  BY date;