Oracle SQL return sum of multiple across tables

Question

Farily new to SQL and the IT world, and always do a lot of work before asking for help but this time I am stumped.

I have three tables:

So I am trying to update the "payment_owed" table by doing the following: For each customer, get the food_id and quantity, then using the food_id multiply the quantity by the cost.

The best I have done this far is a natural join on the tables, and attempting to sum the quantity * cost for each ID

My understanding for updating a specific customer:

update customer
set payment_owed = (select <quantity>) * (select <cost>)
where cust_no = 1;

If im not on the right forum or there's a better place to ask these questions let me know, thank you for your time!

Are you using MS SQL Server or Oracle? (Don't tag products not involved.) — jarlh
– jarlh, Commented Oct 6, 2017 at 14:18
I removed all the product specific tags. Please add back the one that you are actually using. — Sean Lange
– Sean Lange, Commented Oct 6, 2017 at 14:19
On a sidenote: You mention natural joins. My advice: Don't ever use them! They are prone to crash queries once columns get added to the tables. I don't know why natural joins were ever introduced in the standard. That was a bad idea from the start. — Thorsten Kettner
– Thorsten Kettner, Commented Oct 6, 2017 at 14:57
Hi Thorsten, thank you for the advice! I did not know that, I'll make it a practice to look for disadvantages of methods before I blindly use them, thank you! — Andrew sigma
– Andrew sigma, Commented Oct 6, 2017 at 15:58

Lukasz Szozda · Accepted Answer · 2017-10-06 14:26:31Z

2

Simply:

update customer
set payment_owed = (SELECT SUM(o.cost*s.quantity) 
                   FROM order o JOIN session s ON s.food_id = o.food_id         
                   WHERE s.cust_no = customer.cust_no)
where cust_no = 1;

Anyway will you update it after every change on table? How about using view like:

CREATE VIEW my_view AS
SELECT cust_no, SUM(o.cost*s.quantity) 
FROM order o 
JOIN session s ON s.food_id = o.food_id
GROUP BY cust_no;

edited Oct 6, 2017 at 14:26

answered Oct 6, 2017 at 14:19

Lukasz Szozda

181k26 gold badges278 silver badges326 bronze badges

Sign up to request clarification or add additional context in comments.

4 Comments

jarlh Over a year ago

A view is great, or a computed column!

Thorsten Kettner Over a year ago

I'd change this from WHERE s.cust_no = 1 to WHERE s.cust_no = customer.cust_no, so it still works when the main query's where clause gets changed or removed..

Lukasz Szozda Over a year ago

@ThorstenKettner Yes, the first version before edit was using correlated subquery too. I will change to original version.

Andrew sigma Over a year ago

This makes a lot of sense, and I apologise for all the mistakes I made asking this question, I'll be better at it next time! It's exactly what I was looking for! thank you very much for your time everyone

Collectives™ on Stack Overflow

Oracle SQL return sum of multiple across tables

1 Answer 1

4 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

4 Comments

Your Answer

Sign up or log in

Post as a guest

Related