I tried to use a variable as I found in several places on the web:
Expand|Select|Wrap|Line Numbers
- SELECT @total := total + value AS `Running Total`
More specifically, I have a series of harvest records. Each harvest records records a product ID and a weight. A product table lists names for the products; a product value lists prices per weight.
The query sums weights for each product, then multiplies that by the price per weight. So now I have a table with the sum of the prices for each product, but I'm unable to then sum all those sums to get a grand total.
Any thoughts?
Here is the SQL I'm using to build the per-product sums. The `RT` column yields NULLS in this particular attempt:
Expand|Select|Wrap|Line Numbers
- SELECT
- product AS ID,
- MAX(s_product.name) AS Name,
- resource AS Resource,
- CONCAT(sum(quantity),' ',min(harvest.units)) AS Quantity,
- CONCAT('$', ROUND((SUM(quantity) * prices.price), 2),' ',prices.market_type) AS Value,
- @rt := @rt + (ROUND((SUM(quantity) * prices.price), 2)) AS RT,
- COUNT(*) AS Harvests,
- MIN(date) AS Begin,
- MAX(date) AS End,
- notes AS Notes
- FROM
- s_product_harvest harvest
- INNER JOIN
- s_product on s_product.ID = harvest.product
- LEFT OUTER JOIN
- s_product_market_prices prices ON ID = prices.product_ID
- WHERE
- date >= '2009-01-01'
- AND
- date <= '2009-12-31 23:59:59'
- GROUP BY
- product
- ORDER BY
- s_product.name
http://www.EcoReality.org/wiki/2009_harvest
Thanks for any advice or ideas offered!