Mysql SUM with case statement and multiple conditions-CodePudding

SUM( CASE WHEN items.items_status != 2 THEN items.items_amount 
ELSE 0
END) as ItemsAmount
FROM orders 
LEFT JOIN items ON orders.orders_id = items.orders_id
WHERE orders.orders_status = 1 
group by orders.orders_id 


I get the following:


|     orders_id       |    orders_amount |    orders_status  |     ItemsAmount  |
|---------------------|------------------|------------------ |------------------|
|          1          |         10500    |     1             |      10500       |
|          2          |         2500     |     1             |      1300        |

I would like to exclude orders when orders_amount = ItemsAmount . So i have to add another conditions and the first line should be excluded

My two table are ( orders and items ).

table: items

Item_id	orders_id	items_amount	items_staus
1	1	10500	1
2	2	100	1
3	2	1200	1
4	2	200	2
5	3	5000	1

Table : orders

orders_id	orders_status	orders_amount
1	1	10500
2	2	2500
3	2	7000

CodePudding user response：

You may try applying this filter using having clause eg

SUM( CASE WHEN items.items_status != 2 THEN items.items_amount 
ELSE 0
END) as ItemsAmount
FROM orders 
LEFT JOIN items ON orders.orders_id = items.orders_id
WHERE orders.orders_status = 1 
group by orders.orders_id 

HAVING SUM( CASE WHEN items.items_status != 2 THEN items.items_amount 
ELSE 0
END) != orders_amount

CodePudding user response：

Easiest way to do this is just add:

having orders_amount <> ItemsAmount

after the group by.