Cost per box is in the table named products whereas no of boxes is in sales table. PID is common in both tables both are in same schema and database. I want total cost of boxes per PID shown against PID(product id). PID is the primary key.
I was trying this but it didn't work as a syntax
SELECT s.PID, pr.Boxes, (s.Boxes*pr.Cost_per_box) AS Total_Cost_of_Boxes
join products pr
on pr.PID = s.PID;
I am a beginner so pardon anything silly in the syntax and please provide me the right syntax. One more thing pr.cost_per_box is FLOAT and s.Boxes is NUMERIC. pr. represents products table and s. represents sales table. And I have passed syntaxes before hand SELECT * from sales; and SELECT * from products; .
SELECT s.PID, pr.Boxes, (s.Boxes*pr.Cost_per_box) as Total_Cost_of_Boxes
join products pr
on pr.PID = s.PID;
was expecting what I mentioned in details. PID | BOXES | TOTAL COST OF BOXES
You have a sales and a product table and you need to join the two. Yet, in your query, you forgot to specify that you want to select from sales s join products p ..., you only added the join part, but not the from and you had the syntax error problem you reported, but it is expected if you have a join without a from in your select.
So here's a better query:
SELECT pr.PID, coalesce(sum(s.Boxes), 0) as boxes, coalesce(sum(s.Boxes*pr.Cost_per_box), 0) AS Total_Cost_of_Boxes
from products pr
left join sales s
on pr.PID = s.PID
group by pr.PID
Explanation:
products, rather than from sales, as we can be sure that there are no sales without products, so it's better to have products as the point of departuresales on matching PID values, so for each products we will either have one or more actual sales record, or an all null sales record representing the nonexistent sales of a product that was not sold yet so farproducts fields so each record will represent a product with its sales dataBoxes of sales to see how many boxes were sold and coalesce it to 0 if there are no sales records for the product