pg.ddx.io pgsql-sql@postgresql.org mailing list archive
help / color / mirror / Atom feedSUM the result of a subquery.
4+ messages / 2 participants
[nested] [flat]
* SUM the result of a subquery.
@ 2010-09-02 12:17 negora <negora@negora.com>
2010-09-02 12:28 ` Re: SUM the result of a subquery. Jayadevan M <Jayadevan.Maymala@ibsplc.com>
0 siblings, 1 reply; 4+ messages in thread
From: negora @ 2010-09-02 12:17 UTC (permalink / raw)
To: pgsql-sql
<!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>
^ permalink raw reply [nested|flat] 4+ messages in thread
* Re: SUM the result of a subquery.
2010-09-02 12:17 SUM the result of a subquery. negora <negora@negora.com>
@ 2010-09-02 12:28 ` Jayadevan M <Jayadevan.Maymala@ibsplc.com>
2010-09-02 12:36 ` Re: SUM the result of a subquery. negora <negora@negora.com>
0 siblings, 1 reply; 4+ messages in thread
From: Jayadevan M @ 2010-09-02 12:28 UTC (permalink / raw)
To: negora <negora@negora.com>; +Cc: pgsql-sql; pgsql-sql-owner@postgresql.org
> 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?
>
I don't have a PostgreSQL server to try this right now. But you are
looking for something like
SELECT SUM (cost) from (
(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)
) as x
Regards,
Jayadevan
DISCLAIMER:
"The information in this e-mail and any attachment is intended only for
the person to whom it is addressed and may contain confidential and/or
privileged material. If you have received this e-mail in error, kindly
contact the sender and destroy all copies of the original communication.
IBS makes no warranty, express or implied, nor guarantees the accuracy,
adequacy or completeness of the information contained in this email or any
attachment and is not liable for any errors, defects, omissions, viruses
or for resultant loss or damage, if any, direct or indirect."
^ permalink raw reply [nested|flat] 4+ messages in thread
* Re: SUM the result of a subquery.
2010-09-02 12:17 SUM the result of a subquery. negora <negora@negora.com>
2010-09-02 12:28 ` Re: SUM the result of a subquery. Jayadevan M <Jayadevan.Maymala@ibsplc.com>
@ 2010-09-02 12:36 ` negora <negora@negora.com>
0 siblings, 0 replies; 4+ messages in thread
From: negora @ 2010-09-02 12:36 UTC (permalink / raw)
To: Jayadevan M <Jayadevan.Maymala@ibsplc.com>; +Cc: pgsql-sql
<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN">
<html>
<head>
<meta content="text/html; charset=ISO-8859-1"
http-equiv="Content-Type">
</head>
<body bgcolor="#ffffff" text="#000000">
<font face="Verdana">Wow, I had no idea about this kind of SELECT
expression. It works flawless!!! Thank you lots Jayadevan</font><font
face="Verdana"> :) .</font><br>
<font face="Verdana"><br>
</font><br>
On 02/09/10 14:28, Jayadevan M wrote:
<blockquote
cite="mid:OF39AE7609.CC04F696-ON65257792.00446E7D-65257792.00448A26@ibsplc.com"
type="cite">
<blockquote type="cite">
<pre wrap="">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?
</pre>
</blockquote>
<pre wrap="">I don't have a PostgreSQL server to try this right now. But you are
looking for something like
SELECT SUM (cost) from (
(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)
) as x
Regards,
Jayadevan
DISCLAIMER:
"The information in this e-mail and any attachment is intended only for
the person to whom it is addressed and may contain confidential and/or
privileged material. If you have received this e-mail in error, kindly
contact the sender and destroy all copies of the original communication.
IBS makes no warranty, express or implied, nor guarantees the accuracy,
adequacy or completeness of the information contained in this email or any
attachment and is not liable for any errors, defects, omissions, viruses
or for resultant loss or damage, if any, direct or indirect."
</pre>
</blockquote>
</body>
</html>
^ permalink raw reply [nested|flat] 4+ messages in thread
* SUM the result of a subquery.
@ 2010-09-02 12:19 negora <negora@negora.com>
0 siblings, 0 replies; 4+ messages in thread
From: negora @ 2010-09-02 12:19 UTC (permalink / raw)
To: pgsql-sql
<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN">
<html>
<head>
<meta http-equiv="content-type"
content="text/html; charset=ISO-8859-1">
</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>
<br>
</body>
</html>
^ permalink raw reply [nested|flat] 4+ messages in thread
end of thread, other threads:[~2010-09-02 12:36 UTC | newest]
Thread overview: 4+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2010-09-02 12:17 SUM the result of a subquery. negora <negora@negora.com>
2010-09-02 12:28 ` Jayadevan M <Jayadevan.Maymala@ibsplc.com>
2010-09-02 12:36 ` negora <negora@negora.com>
2010-09-02 12:19 SUM the result of a subquery. negora <negora@negora.com>
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