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 wrong.
Any help is really appreciated.
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:
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