Nested MySQL query?

Question

I have this MySQL statement which works fine:

SELECT claim_items.item_id, claim_items.quantity*items_rate.rate as per_item
FROM claim_items, items_rate 
WHERE claim_items.claim_id = 1 AND claim_items.item_id = items_rate.items_id

However what I want to do is to get the sum of the per_item field generated in the MySQL statement. Is it possible to do a nested statement to do that? I know I could create a view and then do it, but would prefer to do it in one statement.

Thanks for your help, much appreciated!

Sachin Shanbhag · Accepted Answer · 2011-03-07 12:23:21Z

2

select t.id, SUM(t.per_item)
from 
(
SELECT claim_items.item_id as id, claim_items.quantity*items_rate.rate as per_item
FROM claim_items, items_rate 
WHERE claim_items.claim_id = 1 AND claim_items.item_id = items_rate.items_id
) t
group by t.id

Hope this should help.

answered Mar 7, 2011 at 12:23

Sachin Shanbhag

55.7k11 gold badges92 silver badges103 bronze badges

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

1 Comment

Jacob Over a year ago

Thanks that helped, I removed the ID though because otherwise it wont calculate the sum :)

Peter Lang · Accepted Answer · 2011-03-07 12:39:14Z

1

If you just want the sum over all rows you can do it like that (using explicit joins for better readability):

SELECT SUM( claim_items.quantity * items_rate.rate )
FROM claim_items
JOIN items_rate ON ( items_rate.items_id = claim_items.item_id )
WHERE claim_items.claim_id = 1

answered Mar 7, 2011 at 12:39

Peter Lang

55.7k29 gold badges153 silver badges165 bronze badges

1 Comment

Jacob Over a year ago

Thanks that does indeed looks nicer :)

Collectives™ on Stack Overflow

Nested MySQL query?

2 Answers 2

1 Comment

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related