Mysql spliting and adding a column based on order status

Question

Here is my query which I want to modify

select 
o.orders_id, 
o.customers_id, 
o.customers_name, 
s.orders_status_name, 
ot.text as order_total, 
ot.value as value,
datediff(now(), payment_data_read_status) as numdaysleft, 
sum(ifnull(op.paid_total, 0)) paid_total 
from orders o 
join orders_total ot 
on o.orders_id = ot.orders_id 
join orders_status s 
on o.orders_status = s.orders_status_id 
left outer join ( select orders_id, sum(ifnull(paid_amount, 0)) as paid_total from orders_payment where confirm_payment = '1' group by orders_id ) op on op.orders_id = o.orders_id where ot.class = 'ot_total' and s.language_id = '1' and round(ot.value,2) != round(ifnull(op.paid_total, 0), 2) 
GROUP by o.customers_id 
ORDER BY paid_total DESC

Each customer has its own orders with different statuses like orders complete, injecting, waiting for payment to confirm etc, What I want to achieve is to sum the value of ot.value for all the orders of each customer with s.orders_status_name= "Order Complete" and show them in a new column as 'completed_orders_sum'. Similarly the orders which are not in "Order Complete" status should add up ot.value and show in new column as 'incompleted_orders_sum'

here is the data for each client

Answer 1

You could use a

sum(case when s.orders_status_name= "Order Complete" 
      then ot.value
      else 0
end) as completed_orders_sum

.

select 
o.orders_id, 
o.customers_id, 
o.customers_name, 
s.orders_status_name, 
ot.text as order_total, 
sum(case when s.orders_status_name= "Order Complete" 
      then ot.value
      else 0
end ) as completed_orders_sum
datediff(now(), payment_data_read_status) as numdaysleft, 
sum(ifnull(op.paid_total, 0)) paid_total 
from orders o 
join orders_total ot 
on o.orders_id = ot.orders_id 
join orders_status s 
on o.orders_status = s.orders_status_id 
left outer join ( select orders_id, sum(ifnull(paid_amount, 0)) as paid_total 
                        from orders_payment 
                        where confirm_payment = '1' group by orders_id ) op on op.orders_id = o.orders_id 
                              where ot.class = 'ot_total' and s.language_id = '1' and round(ot.value,2) != round(ifnull(op.paid_total, 0), 2) 
GROUP by o.customers_id