Adrian Zdanowski Adrian Zdanowski - 1 year ago 83
SQL Question

SQL calculate the total cost of raw materials for each finish product

Hey i am trying to answer this question

Compose a query to calculate the total cost of raw materials for each finish product. In the result table, we would like to see Product ID, Product Name, and its total cost of raw materials. Sort the results by the total cost of raw materials in descending order.

This is what i have

SELECT Product_t.Product_ID, Product_t.Product_Name, SUM(Uses_t.Footage * Raw_Materials_t.Unit_price ) AS Total_Cost_Of_Raw_Materials
FROM Product_t, Uses_t, Raw_materials_t
WHERE Product_t.Product_ID=Uses_t.Product_ID and Uses_t.Material_ID=Raw_materials_t.Material_ID
ORDER BY SUM(Raw_Materials_t.Unit_price * Uses_t.Footage) DESC;

The error pops up that The specified field Product_ID could refer to more than one table listed in the FROM clause of your SQL statement.

I thought i fixed that but clearly its not working
Thanks for the help!!

Answer Source

MS Access is a lousy database to use if you are learning SQL. It has peculiarities. I think the query would look like this:

SELECT p.Product_ID, p.Product_Name,
       SUM(u.Footage * m.Unit_price) AS Total_Cost_Of_Raw_Materials
FROM (Product_t as p INNER JOIN
      Uses_t as u
      ON p.Product_ID = u.Product_ID
     Raw_materials_t as m
     ON u.Material_ID = m.Material_ID
GROUP BY p.Product_ID, p.Product_Name, 
ORDER BY SUM(m.Unit_price * u.Footage) DESC;
Recommended from our users: Dynamic Network Monitoring from WhatsUp Gold from IPSwitch. Free Download