using 5.0.24
mysql> describe order_details;
+------------+-----------------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+------------+-----------------------+------+-----+---------+----------------+
| id | mediumint(8) unsigned | NO | PRI | NULL |
auto_increment |
| order_id | mediumint(8) unsigned | NO | MUL | | |
| product_id | smallint(5) unsigned | NO | MUL | | |
| quantity | smallint(5) unsigned | NO | | | |
| unit_price | decimal(8,2) unsigned | NO | | | |
+------------+-----------------------+------+-----+---------+----------------+
5 rows in set (0.01 sec)
mysql> SELECT order_id, SUM(quantity * unit_price) AS subtotal
-> FROM order_details GROUP BY order_id;
+----------+----------+
| order_id | subtotal |
+----------+----------+
| 1 | 101.94 |
| 2 | 47.97 |
+----------+----------+
2 rows in set (0.00 sec)
-- same query used in LEFT JOIN clause:
mysql> SELECT po.id, po.customer_id, po.delivered, od.subtotal
-> FROM purchase_order AS po
-> LEFT JOIN (SELECT order_id, SUM(quantity * unit_price)
-> AS subtotal FROM order_details GROUP BY order_id)
-> AS od ON od.order_id = po.id
-> ORDER BY po.id\G
*************************** 1. row ***************************
id: 10001
customer_id: 1
delivered: 2007-10-10 23:51:32
subtotal: NULL
*************************** 2. row ***************************
id: 10002
customer_id: 2
delivered: 2007-10-10 23:51:32
subtotal: NULL
2 rows in set (0.00 sec)
So, i'm wondering why this inner query is returning NULL for subtotal
here. I know that SUM() returns NULL on error but i can't see how that's
the situation here.
This works fine with Postgres. Is this a bug, possibly?
brian
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
