CODEWITHSUNDEEP
mysqlsql
mrjasmin
mrjasmin

Reputation: 1270

SQL query joining rows with same value

I need to write a query that creates a view that calculates the total cost of each sale, by considering quantity and price of each bought item. The view should return the debit and total cost.

In the answer each debit-number should only occur once.

Thanks in advance

Table ITEM:

ID       NAME       PRICE
118      Jeans      100
120      Towel      20
127      Shirt      55

Table DEBIT:

DEBIT     ITEM     Quantity
100581    118      5
100581    120      1
100586    127      5

Upvotes: 3

Views: 2593

Answers (3)

Ramandeep Singh
Ramandeep Singh

Reputation: 5253

something like this would work...

SELECT d.id,SUM(i.price*d.quantity) as total_cost
FROM item i join debit d 
on i.id=d.item_id 
group by d.id;

Upvotes: -2

Joachim Isaksson
Joachim Isaksson

Reputation: 180917

You can try this using a simple JOIN;

SELECT d.DEBIT, SUM(d.Quantity*i.Price) SUM
FROM DEBIT d
JOIN ITEM i ON d.ITEM=i.ID
GROUP BY d.DEBIT;

Simple demo here.

Upvotes: 3

user1191247
user1191247

Reputation: 12998

How about -

SELECT DEBIT.DEBIT, SUM(`ITEM`.`PRICE` * `DEBIT`.`Quantity`)
FROM `ITEM`
INNER JOIN `DEBIT`
    ON `ITEM`.`ID` = `DEBIT`.`ITEM`
GROUP BY `DEBIT`.`DEBIT`

Upvotes: 2

Related Questions