CODEWITHSUNDEEP
mysqlsqldatabase
Dipendra Gurung
Dipendra Gurung

Reputation: 5870

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.

OK here is the sql insert statment to clarify,

INSERT INTO `ticket` (`id`, `code`, `cust_name`) VALUES
(1, 'TK01', 'Dipendra Gurung');

INSERT INTO `passenger` (`id`, `ticket_id`, `name`, `age`, `gender`, `fare`) VALUES
(1, 1, 'John', '28', 'M', 120),
(2, 1, 'Kelly', '25', 'F', 120);

INSERT INTO `services` (`id`, `passenger_id`, `item`, `cost`) VALUES
(1, 1, 'S1', 30),
(2, 1, 'S2', 50),
(3, 2, 'S3', 50);

I want to get the total cost of the ticket 'TK01' (including total fare and services total). The sql must return total fare as 120+120 = 240 and total services as 30+50+50 = 130.

Thanks! :)

Upvotes: 11

Views: 108237

Answers (6)

Noel
Noel

Reputation: 10525

How about this?

with fares as(
select p.id id, p.ticket_id ticket_id, sum(coalesce(s.cost,0)) cost
  from passenger p left outer join service s
       on p.id = s.passenger_id
 group by p.id, p.ticket_id)
select q.ticket_id, sum(f.cost), sum(q.fare), sum(f.cost + q.fare)
  from fares f inner join passenger q
       on f.id = q.id
 group by q.ticket_id;

Upvotes: 1

peterm
peterm

Reputation: 92785

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_cost
FROM 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_cost
FROM 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

Just to get a grand total per ticket

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_total
FROM 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

Upvotes: 26

Anurag
Anurag

Reputation: 137

SELECT 
    SUM(fare) as total_fare,
    SUM(cost) as total_service_cost
FROM
    ticket
left join passenger ON ticket.id = passenger.ticket_id
left join service ON passenger.id = service.passenger_id

Upvotes: 2

roman
roman

Reputation: 117390

select
    t.code,
    sum(p.fare) as total_fare,
    sum(s.cost) as total_cost
from ticket as t
    inner join passenger as p on p.ticket_id = t.id
    inner join service as s on s.passenger_id = p.id
group by t.code

Upvotes: 0

StanislavL
StanislavL

Reputation: 57381

What about this?

SELECT 
    SUM(fare) as total_fare,
    SUM(cost) as total_cost as total_service_cost
FROM
    ticket
JOIN passenger ON passenger.ticket_id = ticket.id
JOIN service ON passenger.id = service.passenger_id

If you need to sum for each ticket then add GROUP BY ticket.id

Upvotes: 0

SpaceDog
SpaceDog

Reputation: 3269

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_cost
FROM 
    ticket t, passenger p, service s
WHERE t.id = p.ticket_id AND s.passenger_id = p.id 
GROUP BY t.id;

Upvotes: 3

Related Questions