mysql - How to count a filed with specific condition?

Question

date	train	condition1	condition2
1	train1	0	false
2	train2	1	true
1	train3	1	true
1	train4	5	false

question from:https://stackoverflow.com/questions/65890031/how-to-count-a-filed-with-specific-condition

Answer 1

Just conditional aggregation:

select date, count(*) as cnt_train,
       sum(condition1 > 0) as cnt_condition1,
       sum(condition2) as cnt_condition2
from t
group by date;

EDIT:

This uses the MySQL extension that treats boolean values as numbers in a number context, with 1 for true and 0 for false. This works for any boolean expression, so you can use more complex ones, such as:

       sum(condition1 > 0 and condition2) as cnt_condition1_and_condition2