Mysql Question posted in
The official MySQL documentation can be found here.

Mysql – How to Display total cost of boxes i.e (no_of_boxes*cost_per_box) against common PID i.e. Product-ID column contained in two different tables?

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

Tags:

2

Answers


  1. 0

    Create tables

    CREATE TABLE sales (
  id INTEGER PRIMARY KEY,
  product_id INTEGER NOT NULL,
  boxes numeric NOT NULL
);

CREATE TABLE products (
  id INTEGER PRIMARY KEY,
  name varchar(255) NOT NULL,
  price_for_box FLOAT NOT NULL
);

    then add data

    INSERT INTO products VALUES (1, 'chair', 10.5);
INSERT INTO products VALUES (2, 'table', 22);
INSERT INTO products VALUES (3, 'bed', 100);
INSERT INTO sales VALUES (1, 1, 4);
INSERT INTO sales VALUES (2, 1, 5);
INSERT INTO sales VALUES (3, 2, 5);
INSERT INTO sales VALUES (4, 3, 100);

    now you select data with joins, remember use "from"

    SELECT
   p.name,
   SUM(s.boxes) as 'total_boxes',
   SUM(s.boxes) * p.price_for_box as 'total_cost'
from products p
left join sales s on s.product_id = p.id
group by
 p.id;

    result

    +-------+-------------+------------+
| name  | total_boxes | total_cost |
+-------+-------------+------------+
| chair |           9 |       94.5 |
| table |           5 |        110 |
| bed   |         100 |      10000 |
+-------+-------------+------------+
    Login or Signup to reply.
  2. 0

    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:

    • we select from 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 departure
    • we left join by sales 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 far
    • we group by products fields so each record will represent a product with its sales data
    • we sum the Boxes of sales to see how many boxes were sold and coalesce it to 0 if there are no sales records for the product
    • similarly, we sum the box number * unit price values, but default to 0 if there are no sales
    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search