Unexpected GROUP BY result

[Andrew Threlfall]

I'm getting unexpected results when using GROUP BY. If you specify a 
WHERE match in the query I get the desired result, but if I remove it, 
to show all results, the set returned is inaccurate.

The query counts how many of a certain bcode there are at various prices.

mysql> SELECT bcode, price, sum(qty) FROM bookorders WHERE bcode='AYL' 
GROUP BY price ORDER BY bcode,price;
+-------+-------+----------+
| bcode | price | sum(qty) |
+-------+-------+----------+
| AYL   |  2.00 |        2 |
| AYL   |  3.00 |        5 |
| ayl   |  3.50 |        1 |
| ayl   |  5.00 |       15 |
| ayl   |  6.30 |       86 |
| ayl   |  6.99 |       22 |
| ayl   |  7.00 |      140 |
| ayl   | 63.00 |        1 |
+-------+-------+----------+
8 rows in set (0.07 sec)

now with "WHERE bcode='AYL'" removed:

mysql> SELECT bcode, price, sum(qty) FROM bookorders GROUP BY price 
ORDER BY bcode,price;
+--------+--------+----------+
| bcode  | price  | sum(qty) |
+--------+--------+----------+
| 1tscd  |  20.00 |       29 |
| ach    |  67.50 |        2 |
| ACH    |  75.00 |      252 |
| ar+des |  10.15 |      220 |
| ar+des |  10.35 |        8 |
| ar+des |  10.47 |        8 |
| ar+des |  10.70 |        2 |
| ayl    |   6.30 |       87 |
| ayl    |   6.99 |       25 |
| bt     |   7.50 |        1 |
| bt     |  13.50 |       43 |
| BT     |  14.25 |        1 |
--CUT--
| uys    | 125.00 |      264 |
| uys    | 178.20 |        2 |
+--------+--------+----------+
110 rows in set (0.06 sec)

I'm not getting the results for bcodes at the lower prices, and the two 
results I am getting for ayl in the second case have different sum(qty) 
values.

I can't see what's worng with the query. Am I missing somthing obvious?

This is also the case if I remove ORDER BY in the query.
I'm using 4.0.15 (4.0.18 seems to give same result).
--
Andrew Threlfall
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:    http://lists.mysql.com/[EMAIL PROTECTED]