Monday, 12 August 2013

SQL SUM operation of multiple subqueries

SQL SUM operation of multiple subqueries

i have the following mysql database table designed,
ticket(id, code, cust_name);
passenger(id, ticket_id, name, age, gender, fare);
service(id, passenger_id, item, cost);
A ticket can have many passenger and each passenger can have multiple
services purchased. What I want is to get the grand total of each ticket
cost.
I have tried the following sql,
SELECT
SUM(fare) as total_fare,
(SELECT SUM(cost) as total_cost FROM services WHERE passenger.id =
services.passenger_id) as total_service_cost
FROM
ticket
JOIN passenger ON passenger.ticket_id = ticket.id
Though, the result gets the total of passenger fare as total_fare but for
the service cost, it sums and returns the first passenger's total service
cost only.
I thinks i need some more nesting of queries, need help and if possible
please how can i get the result as grand total summing up both passenger
fare and service cost total.

No comments:

Post a Comment