CODEWITHSUNDEEP
mysql
Santosh Achari
Santosh Achari

Reputation: 3006

Getting specific counts in Mysql using case / if

I am trying to device an incentive plan for my sales agents depending on number of orders, and which shift they worked in.

The sales table (mysql) has the following columns:

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

I want to have write a query to fetch result with following columns (I use orders and sales interchangeably):

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

I understand that the query would have group by, case and perhaps if/else in the select, but can't frame it properly. Please help.

Upvotes: 1

Views: 45

Answers (2)

AdamMc331
AdamMc331

Reputation: 16691

You can use conditional aggregation for a lot of these. In other words, put the condition inside of a SUM() function to get the count of rows matching certain conditions:

SELECT employee, 
   SUM(is_cancelled <> 1) AS totalOrders, 
   SUM(sale_amount < 100 AND is_cancelled <> 1) AS orders_below_100,
   SUM(sale_amount > 10 AND is_cancelled <> 1) AS orders_above_100,
   SUM(sale_time < '22:00:00' AND is_cancelled <> 1) AS orders_dayshift,
   SUM(sale_time > '22:00:00' AND is_cancelled <> 1) AS orders_nightshift,
   SUM(is_cancelled = 1) AS totalCanceled
FROM sales
GROUP BY employee;

Upvotes: 2

sgeddes
sgeddes

Reputation: 62831

You are looking to use conditional aggregation -- here are a couple examples:

select employee, 
       sum(case when is_cancelled != 1 then 1 else 0 end) total_orders,
       sum(case when sale_amount < 100 then 1 else 0 end) orders_below_100dollars,
       sum(case when sale_amount >= 100 then 1 else 0 end) orders_above_100dollars,
       ...
from sales
group by employee

I'm not exactly sure what constitutes daytime vs nighttime, but it should be easy to add given the above.

Upvotes: 3

Related Questions