CODEWITHSUNDEEP
mysqlsql
Pavel
Pavel

Reputation: 4410

sum column with sum of joined table

I have two tables appointments and line_items. 

appointments 
id integer primary key 
price decimal

line_items 
id integer primary key 
price decimal 
appointment_id integer foreign key

I need to get sum of appointments.price with sum of associated sum of line_items price.

For example, I have 2 appointments

id price 
1 10 
2 15

and line items

id price appointment_id
1  10    1
2  15    1
3  10    2
3  20    2

The total result should be (10 + (10 + 15)) + (15 + (10 + 20))

Currently I am trying this query. But it doesn't work.

SELECT SUM(appointments.price + sum(line_items.price)) AS total_sum
FROM `appointments`
  INNER JOIN 'line_items' ON 'line_items'.'appointment_id' = 'appointments'.'id'

What is best approach for this? Subquery for sum of line_items.price or joining sum line_items.price. Or there is another better solution?

Thanks in advance!

Upvotes: 0

Views: 85

Answers (3)

Christian Barron
Christian Barron

Reputation: 2755

This should give you what you're looking for, it seems you were close to the answer:

SELECT SUM(app.price + IFNULL(li.price,0)) AS total_sum
FROM appointments app
  LEFT JOIN 
  (Select appointment_id, Sum(price) price from line_items group by appointment_id) li on li.appointment_id = app.id

Upvotes: 1

ughai
ughai

Reputation: 9890

I think what you are looking for is a SUM() GROUP BY in a derived table and then a join with appointments. Something like this.

SELECT SUM(appointments.price + line_items.price) AS total_sum
FROM appointments
 INNER JOIN 
(
SELECT line_items.appointment_id,SUM(price) as price
line_items
GROUP BY appointment_id 
) line_items ON line_items.appointment_id = appointments.id

Upvotes: 2

SimarjeetSingh Panghlia
SimarjeetSingh Panghlia

Reputation: 2200

Try this

 SELECT appointments.id,(appointments.price + sum(line_items.price)) AS total_sum
 FROM appointments
 INNER JOIN line_items ON line_items.appointment_id = appointments.id
 group by appointments.id

Upvotes: 0

Related Questions