Do aggregate MySQL functions always return a single row?
You need to use GROUP BY
as such to get your desired result:
SELECTorder_id,part_id,SUM(cost) AS totalFROM orders WHERE order_date BETWEEN xxx AND yyyGROUP BY order_id, part_id
This will group your results. Note that since I assume that order_id
and part_id
is a compound PK, SUM(cost)
in the above will probably be = cost
(since you a grouping by a combination of two fields which is guarantied to be unique. The correlated subquery below will overcome this limitation).
Any non-aggregate rows fetched needs to be specified in the GROUP BY
row.
For more information, you can read a tutorial about GROUP BY
here:
EDIT: If you want to use a column as both aggregate and non-aggregate, or if you need to desegregate your groups, you will need to use a subquery as such:
SELECTor1.order_id,or1.cost,or1.part_id,( SELECT SUM(cost) FROM orders or2 WHERE or1.order_id = or2.order_id GROUP BY or2.order_id) AS totalFROM orders or1WHERE or1.order_date BETWEEN xxx AND yyy