SQL SUM operation of multiple subqueries

First of all in your current table schema you have no way to distinguish between services that have been sold to the same passenger in different tickets. Therefore you have no way to correctly calculate total_cost per ticket. You have to have ticket_id in your service table.

Now, if you were to have a ticket_id in service table then a solution with a correlated subqueries might look like

SELECT t.*,       (SELECT SUM(fare)           FROM passenger         WHERE ticket_id = t.id) total_fare,       (SELECT SUM(cost)           FROM service         WHERE ticket_id = t.id) total_cost  FROM ticket t

or with JOINs

SELECT t.id,        p.fare total_fare,       s.cost total_costFROM ticket t LEFT JOIN (  SELECT ticket_id, SUM(fare) fare    FROM passenger   GROUP BY ticket_id) p   ON t.id = p.ticket_id LEFT JOIN (  SELECT ticket_id, SUM(cost) cost    FROM service   GROUP BY ticket_id) s  ON t.id = s.ticket_id

Note: Both queries take care of the fact that passenger can have multiple services per ticket or no services at all.

Now with your current schema

SELECT t.*,       (SELECT SUM(fare)           FROM passenger         WHERE ticket_id = t.id) total_fare,       (SELECT SUM(cost)           FROM service s JOIN passenger p            ON s.passenger_id = p.id         WHERE p.ticket_id = t.id) total_cost  FROM ticket t

and

SELECT t.id,        p.fare total_fare,       s.cost total_costFROM ticket t LEFT JOIN (  SELECT ticket_id, SUM(fare) fare    FROM passenger   GROUP BY ticket_id) p   ON t.id = p.ticket_id LEFT JOIN (  SELECT p.ticket_id, SUM(cost) cost    FROM service s  JOIN passenger p      ON s.passenger_id = p.id   GROUP BY p.ticket_id) s  ON t.id = s.ticket_id

SELECT t.*,       (SELECT SUM(fare)           FROM passenger         WHERE ticket_id = t.id) +       (SELECT SUM(cost)           FROM service s JOIN passenger p            ON s.passenger_id = p.id         WHERE p.ticket_id = t.id) grand_total  FROM ticket t

or

SELECT t.id,        p.fare + s.cost grand_totalFROM ticket t LEFT JOIN (  SELECT ticket_id, SUM(fare) fare    FROM passenger   GROUP BY ticket_id) p   ON t.id = p.ticket_id LEFT JOIN (  SELECT p.ticket_id, SUM(cost) cost    FROM service s  JOIN passenger p      ON s.passenger_id = p.id   GROUP BY p.ticket_id) s  ON t.id = s.ticket_id

You can just join the three tables together, then you can do the SUMs directly without the subselect. You'll need to use GROUP BY to group by ticket.id if you want it per ticket.

Something like:

SELECT t.id, SUM(p.fare) AS total_far, SUM(s.cost) AS total_costFROM     ticket t, passenger p, service sWHERE t.id = p.ticket_id AND s.passenger_id = p.id GROUP BY t.id;

SELECT     SUM(fare) as total_fare,    SUM(cost) as total_service_costFROM    ticketleft join passenger ON ticket.id = passenger.ticket_idleft join service ON passenger.id = service.passenger_id