Return count from different arguments-CodePudding

Let's say i have a table like this

ID NAME     DATE     STATUS 
1  FIRST  2021-01-01   1
1  SECOND 2021-01-02   1
1  THIRD  2021-01-01   0
1  FOURTH 2021-01-02   0
1  FIFTH  2021-01-02   1

and i want to return the count of total rows, plus a count from rows with a given status (lets say 1), all grouped by date.

So final result would be like :

CountRows CountStatus    Date
   2          1       2021-01-01
   3          2       2021-01-02

What's the logic and sintaxe for this query if it's possible.

CodePudding user response：

You can do aggregation :

select count(*) as countrows, sum(status) as countstatus, date
from t
group by date;

CodePudding user response：

If status can take on values other than 0 or 1, you would want explicit logic:

select count(*), sum(case when status = 1 then 1 else 0 end), date
from t
group by date;