<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN">
<html>
<head>
<meta http-equiv="content-type" content="text/html; charset=UTF-8">
</head>
<body bgcolor="#ffffff" text="#000000">
<font face="Verdana">Hello:<br>
<br>
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:</font><font
face="Verdana"><br>
</font>
<blockquote><font face="Verdana">SELECT i.id_item, </font><font
face="Verdana"> i.price, </font><font face="Verdana">SUM
(o.quantity), </font><font face="Verdana">ROUND (SUM (o.quantity) *
i.price, 2) AS cost<br>
FROM orders o<br>
JOIN items i ON i.id_item = o.id_</font><font face="Verdana">item</font><br>
<font face="Verdana">WHERE o.date_order BETWEEN '2010-01-01' AND
'2010-01-31'<br>
GROUP BY i.id_</font><font face="Verdana">item</font><font
face="Verdana">, i.</font><font face="Verdana">price</font><font
face="Verdana">;</font></blockquote>
<font face="Verdana"><br>
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</font><font
face="Verdana"> to "lose" as less decimals as possible, because a
rounding is applied on every multiplication.<br>
<br>
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:<br>
</font>
<blockquote><font face="Verdana">SELECT </font><font face="Verdana">SUM
(</font><font face="Verdana">ROUND (o.quantity * i.price, 2))<br>
FROM orders o</font><font face="Verdana"><br>
JOIN items i ON i.id_item = o.id_</font><font face="Verdana">item</font><br>
<font face="Verdana">WHERE o.date_order BETWEEN '2010-01-01' AND
'2010-01-31';</font><br>
</blockquote>
<br>
This multiplies the quantity of every line by the price per unit, and
sums the costs one by one. Done...<br>
<br>
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.<br>
<br>
PostgreSQL doesn't allow nested SUMs, so I tried something like this:<br>
<blockquote><font face="Verdana">SELECT SUM (<br>
</font>
<blockquote><font face="Verdana">(SELECT i.id_item, </font><font
face="Verdana"> i.price, </font><font face="Verdana">SUM
(o.quantity), </font><font face="Verdana">ROUND (SUM (o.quantity) *
i.price, 2) AS cost</font><br>
<font face="Verdana">FROM orders o</font><br>
<font face="Verdana">JOIN items i ON i.id_item = o.id_</font><font
face="Verdana">item</font><br>
<font face="Verdana">WHERE o.date_order BETWEEN '2010-01-01' AND
'2010-01-31'</font><br>
<font face="Verdana">GROUP BY i.id_</font><font face="Verdana">item</font><font
face="Verdana">, i.</font><font face="Verdana">price</font><font
face="Verdana">)<br>
</font></blockquote>
<font face="Verdana">);</font> </blockquote>
<br>
No luck. Obviously SUM expects an expression, not a set of rows. Is
there a way to perform a sum of the resulting rows?<br>
<br>
Thank you a lot.<font face="Verdana"><br>
</font>
</body>
</html>