CODEWITHSUNDEEP
sqldatabasesqlite
Tzhari
Tzhari

Reputation: 23

SQL Query or Table Error?

So Im trying to find the total amount spent on Cuts and Products by each Customer

I don't know if my Query is Wrong or my entire Database Schema any ideas?

My Query 

`Select First_Name, SUM(B.Cost), SUM(C.Cost)
FROM bookings A, cuts B, products C, customers D
Where A.Customer_ID= D.Customer_ID
AND A.Cut_ID = B.Cut_ID
AND A.Product_ID= C.Product_ID;`

My Database

`Table: bookings 
Booking_N0, Customer_ID, Cut_ID, Product_ID, TimeTaken`


`Table: customers
Customre_ID, First_Name, Sex`


`Table: products
Product_ID, Products, Cost`

`Table: cuts
 Cut_ID, Cut, Cost`

Upvotes: 0

Views: 62

Answers (3)

potashin
potashin

Reputation: 44581

You should GROUP BY to SUM by each customer :

Select D.First_Name
     , SUM(B.Cost)
     , SUM(C.Cost)
FROM bookings A LEFT JOIN cuts B ON A.Cut_ID = B.Cut_ID
                JOIN products C ON A.Product_ID = C.Product_ID
                JOIN customers D ON A.Customer_ID = D.Customer_ID
GROUP BY D.First_Name;

Also, look forward using explicit join notation (FROM table1 t1 JOIN table2 t2 ON t1.field1 = t2.field2) instead of implicit join notation (FROM table1 t1, table2 t2 WHERE t1.field1 = t2.field2), because it is has more intuitive view (tables are listed near conditions on which they are joined).

Upvotes: 1

Joachim Weiß
Joachim Weiß

Reputation: 407

If you use aggregate function like SUM you have to add a group by clause in your case: 

 ...
 AND A.Product_ID= C.Product_ID
 GROUP BY First_Name

Upvotes: 0

Mudassir Hasan
Mudassir Hasan

Reputation: 28741

Start using recommended JOIN / ON syntax for joining instead of using WHERE clause . You also need a GROUP BY clause

Select First_Name, SUM(B.Cost), SUM(C.Cost)
FROM bookings A
INNER JOIN cuts B
 ON A.Cut_ID = B.Cut_ID
INNER JOIN products C 
 ON A.Product_ID= C.Product_ID
INNER JOIN customers D
 ON  A.Customer_ID= D.Customer_ID
GROUP BY First_Name

Upvotes: 0

Related Questions