🚀 go-pugleaf

This is a simplified version of my problem (in reality I need the GROUP
BY orders.id to calculate a price)

select
orders.inv_id,
products.price
from orders
LEFT JOIN products ON products.id = orders.prod_id
GROUP BY orders.id, inv_id

returns:


inv_id | price
---------------------------------------------------------
1      | 13
1      | 50
2      | .
2      | .
2      | .
.      | .


And this is what I would like to get:

inv_id | total
--------------
1      | 63
2      | .
.      | .
.      | .


I need to group the result table by inv_id and sum the sum column - I
thought it would be simple, but I can't seem to get it working. I tried
modifying a GROUP BY line to "GROUP BY orders.id, orders.inv_id" (need
to order by orders.id to calculate a subtotal, and orders.inv_id to
calculate the total) but the rows wouldn't group :( Why?! How can I
perform a GROUP BY ona grouped rows? Documentation?
The DB is MySQL but this bight be a ANSI question.


Sent via Deja.com http://www.deja.com/
Before you buy.