From: negora <negora@negora.com>
To: pgsql-sql@postgresql.org
Subject: SUM the result of a subquery.
Date: Thu, 02 Sep 2010 14:17:34 +0200
Message-ID: <4C7F95DE.9020200@negora.com> (raw)
<!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>
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-sql@postgresql.org
Cc: negora@negora.com
Subject: Re: SUM the result of a subquery.
In-Reply-To: <4C7F95DE.9020200@negora.com>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox