How to count a filed with specific condition?

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

I want to create a table to count the trains

  1. group by date
  2. count by conditions
date train count condition1 > 0 condition2 is true condition1 > 0 and condition2 is false
1 1 0 1 0
2 3 2 1 1

How can I achieve so?

mysql sql
1 Answers

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
user_11440350
Related