How to make SQL query better?

Question

SQL

There is an orders table with the following fields:
price - order cost
delivery_price - delivery cost
delivery_type - delivery type (pickup, courier, courier +5 km...)

There is also a table transactions (transactions) - there can be several transactions per order (for example, they chose self-delivery, paid immediately on the site, and then changed it to delivery. And delivery costs money)

Fields
order_id - order id
price - cost per transaction (I think this is understandable)

Need to receive
- all unpaid orders
- all paid orders

I make a request So

...
/* Все оплаченные*/
where orders.price+orders.delivery_price = (select sum(price) from transactions where order_id = orders.id group by order_id) as transactions_price

Maybe there is a more elegant way to get the right orders?
And is it possible to somehow get rid of delivery_price leaving only delivery_type ?

Answer 1

Here is a request for paid orders:

select *
from orders
join (
  select order_id, sum(price) paid
    from transactions 
    group by order_id
) paiments on 
  paiments.order_id = orders.id and 
  (orders.price + orders.delivery_price) =< paiments.paid;

MySQL fiddle
For unpaid ones:

select *
from orders
left join (
  select order_id, sum(price) paid
    from transactions 
    group by order_id
) paiments on 
  paiments.order_id = orders.id 
where (orders.price + orders.delivery_price) > coalecse(paiments.paid, 0);