Getting specific counts in Mysql using case / if -

i trying device incentive plan sales agents depending on number of orders, , shift worked in.

the sales table (mysql) has following columns:

sale_id (int, primary, auto increment) employee (varchar, 255) sale_time (datetime, time sale placed) sale_amount (float 10,2) sales_channel (tinyint, values call (1), walk_in (2),referral(3)) is_cancelled (tinyint, values 1 cancelled, , 0 default) (and few other columns not relevant case) 

i want have write query fetch result following columns (i use orders , sales interchangeably):

employee total_orders (count excluding cancelled sales, i.e. is_cancelled!= 1) orders_below_100dollars (orders count sale_amount below 100 , is_cancelled = 0) orders_above_100dollars (orders count sale_amount above 100 , is_cancelled = 0) orders_dayshift (orders count placed between 9am , before 10pm , is_cancelled = 0) orders_nightshift (orders count placed after 10pm , before next day 9am , is_cancelled = 0) cancelled_orders (orders count is_cancelled = 1) 

i understand query have group by, case , perhaps if/else in select, can't frame properly. please help.

you can use conditional aggregation lot of these. in other words, put condition inside of sum() function count of rows matching conditions:

select employee,     sum(is_cancelled <> 1) totalorders,     sum(sale_amount < 100 , is_cancelled <> 1) orders_below_100,    sum(sale_amount > 10 , is_cancelled <> 1) orders_above_100,    sum(sale_time < '22:00:00' , is_cancelled <> 1) orders_dayshift,    sum(sale_time > '22:00:00' , is_cancelled <> 1) orders_nightshift,    sum(is_cancelled = 1) totalcanceled sales group employee;