CODEWITHSUNDEEP
mysqlsql
Shashidhara
Shashidhara

Reputation: 683

Apply condition with group by

I have one mysql table of following structure. orders ( id, order_status(number), date(timestamp)); Date is updated when status is changed. Select count of all orders using order_status. But one order_status say status id 10, should be selected based on date.

Ex: Consider status id 10 means order completed. Select count of orders with status 10 which is updated today. And all other status counts calculated without any condition.

count | s
------+---
2     | 1
6     | 2
8391  | 10
5     | 7
6     | 15
28    | 18

Upvotes: 0

Views: 85

Answers (2)

Paul Maxwell
Paul Maxwell

Reputation: 35603

Use "conditional aggregates" which are case expressions inside an aggregate function e.g:

select
       count(*) all_orders
     , count(case when order_status  10 then id end) as unclosed_orders
     , count(case when order_status  = 10 then id end) as closed_orders
     , count(case when order_status  = 10 
                  and last_updated_date = current_date()
                  then id end) as closed_today
from your_table

Note: the COUNT() function ignores NULLs

You could simulate the same result using SUM() e.g.:

select
       sum(1) all_orders
     , sum(case when order_status <> 10 then 1 else 0 end) as unclosed_orders
     , sum(case when order_status  = 10 then 1 else 0 end) as closed_orders
from your_table

However I do prefer to use COUNT() if that is what the numbers represent.

Upvotes: 0

Smit Mehta
Smit Mehta

Reputation: 196

Modifying the query I have added the condition for date:

select
       sum(1) all_orders
     , sum(case when order_status <> 10 then 1 else 0 end) as unclosed_orders
     , sum(case when order_status  = 10 AND date=DATE_FORMAT(NOW(),'%Y-%m-%d') then 1 else 0 end) as closed_orders_today
     , sum(case when order_status  = 10 AND date!=DATE_FORMAT(NOW(),'%Y-%m-%d')then 1 else 0 end) as closed_orders_past
from orders

Hope this helps!

EDIT

The Union of two query might get the output you want:

SELECT COUNT(*) as count, order_status as s FROM orders  WHERE order_status='10' AND date=DATE_FORMAT(NOW(),'%Y-%m-%d')
UNION
SELECT COUNT(*) as count, order_status as s FROM orders  WHERE order_status!='10' GROUP BY order_status

1) Query returns the count with status of 10 and are placed today.

2) Query returns the count with status other than 10.

Union of both returns the count and status to get the desired output.

Upvotes: 2

Related Questions