MariaDB 10.3

By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.

CREATE TABLE orders (
  orders_id int auto_increment,
  primary key (orders_id)
);
CREATE TABLE orders_totals (
  fk_orders_id int,
  name varchar(10),
  total decimal(10,4),
  primary key (fk_orders_id, name)
);
CREATE TABLE orders_products (
  orders_products_id int auto_increment,
  fk_orders_id int,
  products_name varchar(100),
  quantity int,
  cost decimal(10,4),
  primary key (orders_products_id)
);
INSERT INTO orders VALUES (1);
INSERT INTO orders_totals VALUES (1, 'Total', 260.00);
INSERT INTO orders_totals VALUES (1, 'Coupon', 10);
INSERT INTO orders_products VALUES (1, 1, 'Nuts', 1, 100.00);
INSERT INTO orders_products VALUES (2, 1, 'Bolts', 1, 120.00);
INSERT INTO orders_products VALUES (3, 1, 'Glue', 1, 50.00);
SELECT 
    orders_id, 
    products_name as name, 
    cost,
    (all_coupons.total / num_products.qty) as coupon,
    cost - (all_coupons.total / num_products.qty) as final_cost
FROM 
    orders o 
JOIN  
    orders_products op 
    on o.orders_id = op.fk_orders_id
LEFT OUTER JOIN 
    (SELECT
         fk_orders_id,
         total 
     FROM 
         orders_totals 
     WHERE 
         name = 'coupon') all_coupons 
     ON o.orders_id = all_coupons.fk_orders_id
LEFT OUTER JOIN 
    (SELECT
         fk_orders_id,
         SUM(quantity) AS qty 
     FROM 
          orders_products 
     GROUP BY 
         fk_orders_id) num_products 
     ON o.orders_id = num_products.fk_orders_id
WHERE o.orders_id = 1



orders_id
name
cost
coupon
final_cost


1
Nuts
100.0000
3.33333333
96.66666667

1
Bolts
120.0000
3.33333333
116.66666667

1
Glue
50.0000
3.33333333
46.66666667

orders_id	name	cost	coupon	final_cost
1	Nuts	100.0000	3.33333333	96.66666667
1	Bolts	120.0000	3.33333333	116.66666667
1	Glue	50.0000	3.33333333	46.66666667