Hello:
I've a question related to the combination of the SUM aggregate
function and subqueries. Is it possible to SUM the resulting rows of a
subquery? I'm explaining why I need this... I've a query like this:
SELECT i.id_item, i.price, SUM
(o.quantity), ROUND (SUM (o.quantity) *
i.price, 2) AS cost
FROM orders o
JOIN items i ON i.id_item = o.id_item
WHERE o.date_order BETWEEN '2010-01-01' AND
'2010-01-31'
GROUP BY i.id_item, i.price;
This just groups items of several orders by item ID, sums the
quantities, multiplies such amounts by the price per unit, and rounds
the result to 2 decimals. Very easy. The cost calculation is performed
using the sum of the quantities instead of doing it per line to "lose" as less decimals as possible, because a
rounding is applied on every multiplication.
Now I need to get the total of ALL that lines in a separate query. It'd
be really simple to do something like this:
SELECT SUM
(ROUND (o.quantity * i.price, 2))
FROM orders o
JOIN items i ON i.id_item = o.id_item
WHERE o.date_order BETWEEN '2010-01-01' AND
'2010-01-31';
This multiplies the quantity of every line by the price per unit, and
sums the costs one by one. Done...
However, I'm obliged by the client to get an EXACT total with NO
DIFFERENCE of decimals (even though lots of them are "lost" during the
rounded multiplications). He wants a total which MATCHES with the
MANUAL sum of the results of the first query. It means that I need to
do the same kind of grouping which I perform on the first query and
then sum all them. Hence, the reason behind my need.
PostgreSQL doesn't allow nested SUMs, so I tried something like this:
SELECT SUM (
(SELECT i.id_item, i.price, SUM
(o.quantity), ROUND (SUM (o.quantity) *
i.price, 2) AS cost
FROM orders o
JOIN items i ON i.id_item = o.id_item
WHERE o.date_order BETWEEN '2010-01-01' AND
'2010-01-31'
GROUP BY i.id_item, i.price)
);
No luck. Obviously SUM expects an expression, not a set of rows. Is
there a way to perform a sum of the resulting rows?
Thank you a lot.