469,273 Members | 1,658 Online
Bytes | Developer Community
New Post

Home Posts Topics Members FAQ

Post your question to a community of 469,273 developers. It's quick & easy.

Grand Totals?

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:
Expand|Select|Wrap|Line Numbers
  1. 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:
Expand|Select|Wrap|Line Numbers
  1. SELECT
  2.     product AS ID,
  3.     MAX(s_product.name) AS Name,
  4.     resource AS Resource,
  5.     CONCAT(sum(quantity),' ',min(harvest.units)) AS Quantity,
  6.     CONCAT('$', ROUND((SUM(quantity) * prices.price), 2),' ',prices.market_type) AS Value,
  7.     @rt := @rt + (ROUND((SUM(quantity) * prices.price), 2)) AS RT,
  8.     COUNT(*) AS Harvests,
  9.     MIN(date) AS Begin,
  10.     MAX(date) AS End,
  11.     notes AS Notes
  12. FROM
  13.     s_product_harvest harvest
  14.   INNER JOIN
  15.     s_product on s_product.ID = harvest.product
  16.   LEFT OUTER JOIN
  17.     s_product_market_prices prices ON ID = prices.product_ID
  18. WHERE
  19.     date >= '2009-01-01'
  20.   AND
  21.     date <= '2009-12-31 23:59:59'
  22. GROUP BY
  23.     product
  24. ORDER BY
  25.     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!
Nov 24 '09 #1
2 2787
mwasif
801 Expert 512MB
Did you try ROLLUP?
Nov 24 '09 #2
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?
Nov 25 '09 #3

Post your reply

Sign in to post your reply or Sign up for a free account.

Similar topics

2 posts views Thread by Darren | last post: by
1 post views Thread by dan.cawthorne | last post: by
1 post views Thread by CARIGAR | last post: by
reply views Thread by zhoujie | last post: by
reply views Thread by suresh191 | last post: by
By using this site, you agree to our Privacy Policy and Terms of Use.