SQL calculate total price

Question

new here (plus sorry for the bad english..), and I have some SQL difficulties with two tables here - Sells and Product:

Product:

ProductID | Product | Price
____________________________
1         | walnuts | 16
2         | cashew  | 25
3         | peanuts | 4

Sells (each product kilograms sold):

Day | walnuts | cashew | hazelnut
__________________________________
1   | 2       | 3      | 1
2   | 8       | 6      | 25
3   | 1       | 3      | 12

What I want to display is the total money earned in day 2 (for example) so how can i calculate that?

Answer 1

Your way of designing table is not good.

**Item table:**

    product_id | product_name | price
    __________________________________
    1          | Some1        | 20
    2          | Some2        | 30
    3          | Some3        | 40

    **sale table :**

    sale_id  |  product_id | quantity 
    __________________________________
    1        | 2           | 2
    2        | 1           | 1
    3        | 3           | 2

Now apply query:

SELECT sale.quantity*item.price as TOTAL FROM item,sale WHERE item.product_id=sale.product_id;

if you have more columns, then you can apply more filters.

Answer 2

1. Change your table structure

Your db table structure is not good. Using the product variety as a column is not the way. Rather use following structure:

Product:

ProductID | Product | Price
____________________________
1         | walnuts | 16
2         | cashew  | 25
3         | peanuts | 4

Sells (each product kilograms sold):

Day | ProductID | Amt    | Price
__________________________________
1   | 1         | 2      | 15
1   | 2         | 3      | 25
1   | 3         | 1      | 4
2   | 1         | 8      | 16
2   | 2         | 6      | 23
2   | 3         | 25     | 5
3   | 1         | 1      | 16
3   | 2         | 3      | 25
3   | 3         | 12     | 4

I put price column in Sells table, because actual sale price can be vary per day.

Once table in this structure, you can do group by to sum up. Following is MySQL query:

SELECT SUM(Amt * Price) AS total FROM Sells WHERE Day=2 GROUP BY Day

If you want to check per day and product with product itself details, you can try following:

SELECT p.ProductID, p.Product, SUM(s.Amt * s.Price) AS total 
    FROM Sells AS s
    LEFT JOIN Product AS p ON s.ProductID = p.ProductID
WHERE s.Day=2 GROUP BY s.Day, s.Product

2. If it's not possible to change, then...

@Kanagaraj Dhanapal 's answer will be yours.