SQL query joining rows with same value

Question

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

I have tried the following: SELECT debid, (item.price * debit.quantity) AS "Total Price" FROM debit, item WHERE debit.item = item.id — mrjasmin
– mrjasmin, Commented Mar 22, 2012 at 11:40

Joachim Isaksson · Accepted Answer · 2012-03-22 11:39:52Z

3

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.

answered Mar 22, 2012 at 11:39

Joachim Isaksson

182k28 gold badges297 silver badges307 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

user1191247 · Accepted Answer · 2012-03-22 11:38:10Z

2

How about -

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

answered Mar 22, 2012 at 11:38

user1191247

13k2 gold badges25 silver badges33 bronze badges

1 Comment

user1191247 Over a year ago

Around here we say thanks by accepting the answer that we use. Please accept @Joachim's answer as he made the effort to set up the demo.

Ramandeep Singh · Accepted Answer · 2012-03-22 11:51:39Z

-2

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;

edited Mar 22, 2012 at 11:51

answered Mar 22, 2012 at 11:38

Ramandeep Singh

5,2833 gold badges31 silver badges34 bronze badges

2 Comments

MatBailie Over a year ago

You don't want to group by i.price,d.quantity.

Ramandeep Singh Over a year ago

yeah..i knw..written by mistake..!!

Collectives™ on Stack Overflow

SQL query joining rows with same value

3 Answers 3

Comments

1 Comment

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

Comments

1 Comment

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related