Björn Persson wrote:
> Noel Stratton:
>
>>As you can see the summary total in the 'Total' column is incorrect. The
>>summary total should be 36 but is spitting out 40. I am not even sure
>>where it is getting 40. The only way to get 40 is if it is multiplying
>>5X8.
>
> Isn't that what you told it to do?
Well, that obviously wasn't his intent.
>>ROUND(price*count(log.product), 2) AS 'Total'
>
> For the summary line that means "Pick a price field at random (as there are
> several rows to choose from) and multiply it with the count of all the
> log.product fields."
How do you know that? Or more appropriately, how was he supposed to know that?
The manual gives the clear impression that WITH ROLLUP simply totals columns.
Based on that, it really wasn't unreasonable to expect that the ROLLUP row for
the price*COUNT(log.product) column would be the total of that column. You are
certainly right that it doesn't work that way, but that is undocumented.
Instead, it added up the COUNTs and then multiplied by a single price. That's
probably correct behavior, but it is certainly counterintuitive.
Gleb Paharenko wrote:
Hello.
In my opinion, you have something wrong with your query in general.
product.price field is in SELECT part and not in GROUP BY, so the
result is not-predictable. See:
http://dev.mysql.com/doc/refman/5.0/en/group-by-hidden-fields.html
I think you've missed the point. I cannot be certain, but it seems clear that
there is a unique price for each product. In that case, the manual page you
reference makes it quite clear that selecting price is perfectly allowable *in
MySQL* with a GROUP BY on product, as there will be no ambiguity. That is, the
value of price for each group is entirely predictable.
The problem comes in the interaction between price and WITH ROLLLUP. It seems
that while MySQL allows extra, unique-valued columns with a GROUP BY, they turn
into nonsense in the ROLLUP results. (Although I note the result is still
predictable, as MySQL seems to simply use the last value found.) Again, that's
probably correct behavior, but it is utterly undocumented.
Michael
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
