Grand Totals?

[Bytesmiths]

I'm using GROUP BY to come up with totals for some items, but would like to either have a grand total at the bottom, or a running total field.

I tried to use a variable as I found in several places on the web:

SELECT @total := total + value AS `Running Total`

but what I'm totalling is an aggregate, and it doesn't seem to work. I tried to explore some options dev.mysql.com, but again, my field seems to be too complex.

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:

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

You can see an example of the working query (without the attempt at a running total) here:

http://www.EcoReality.org/wiki/2009_harvest

Thanks for any advice or ideas offered!

[mwasif]

Did you try ROLLUP?

[Bytesmiths]

Thanks for the pointer, but that won't work with my ORDER BY clause, as stated on the page you linked.

I did try it and verified that it doesn't like my ORDER BY. Since the GROUP column is an opaque reference (INT), I really need to sort on the real names of things, not their arbitrary INT keys.

Also, when I took out the ORDER BY, I got strange results. It summarized the "wrong" column, seemingly giving me a total for the weights, which are a mix of grams, kilograms, and "each," so it's totally useless to sum kilograms of pears with grams of bok choy with numbers of eggs!

I'm going to play with this a bit more, but in the mean time, any other ideas?