database - Do aggregate MySQL functions always return a single row?

Question

I'm sorry if this is really basic, but:

I feel at some point I didn't have this issue, and now I am, so either I was doing something totally different before or my syntax has skipped a step.

I have, for example, a query that I need to return all rows with certain data along with another column that has the total of one of those columns. If things worked as I expected them, it would look like:

 SELECT
 order_id,
 cost,
 part_id,
 SUM(cost) AS total
 FROM orders 
 WHERE order_date BETWEEN xxx AND yyy

And I would get all the rows with my orders, with the total tacked on to the end of each one. I know the total would be the same each time, but that's expected. Right now to get that to work I'm using:

 SELECT
 order_id,
 cost,
 part_id,
 (SELECT SUM(cost)
 FROM orders
 WHERE order_date BETWEEN xxx AND yyy) AS total
 FROM orders 
 WHERE order_date BETWEEN xxx AND yyy

Essentially running the same query twice, once for the total, once for the other data. But if I wanted, say, the SUM and, I dunno, the average cost, I'd then be doing the same query 3 times, and that seems really wrong, which is why I'm thinking I'm making some really basic mistake.

Any help is really appreciated.

Answer 1

You need to use GROUP BY as such to get your desired result:

SELECT
order_id,
part_id,
SUM(cost) AS total
FROM orders 
WHERE order_date BETWEEN xxx AND yyy
GROUP 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:

MySQL Tutorial - Group By

---

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:

SELECT
or1.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 total
FROM orders or1
WHERE or1.order_date BETWEEN xxx AND yyy