From negora@negora.com Thu Sep 2 12:24:26 2010 Received: from maia.hub.org (maia-2.hub.org [200.46.204.251]) by mail.postgresql.org (Postfix) with ESMTP id 4EBCB6329B6 for ; Thu, 2 Sep 2010 09:24:26 -0300 (ADT) Received: from mail.postgresql.org ([200.46.204.86]) by maia.hub.org (mx1.hub.org [200.46.204.251]) (amavisd-maia, port 10024) with ESMTP id 74099-04 for ; Thu, 2 Sep 2010 12:24:17 +0000 (UTC) X-Greylist: delayed 00:06:38.469891 by SQLgrey-1.7.6 Received: from oproxy3-pub.bluehost.com (oproxy3-pub.bluehost.com [69.89.21.8]) by mail.postgresql.org (Postfix) with SMTP id A53C6632369 for ; Thu, 2 Sep 2010 09:24:18 -0300 (ADT) Received: (qmail 19532 invoked by uid 0); 2 Sep 2010 12:17:37 -0000 Received: from unknown (HELO box398.bluehost.com) (69.89.31.198) by oproxy3.bluehost.com with SMTP; 2 Sep 2010 12:17:37 -0000 DomainKey-Signature: a=rsa-sha1; q=dns; c=nofws; s=default; d=negora.com; h=Received:Message-ID:Date:From:User-Agent:MIME-Version:To:Subject:Content-Type:Content-Transfer-Encoding:X-Identified-User; b=GbSUIav1DpoR7ppokZ3B3kkiMEHjpmYgTMOfRGCoVhRLZ7MVSZczYxgeYI2dfyci7LKpkjkt+u421ouvd+33ZFJ+Ve/T1XPk0J4DGkMCF9eg7MaV6J0fcXdRiYz0Y7Fu; Received: from 30.red-80-32-234.staticip.rima-tde.net ([80.32.234.30] helo=[172.16.0.2]) by box398.bluehost.com with esmtpsa (TLSv1:AES256-SHA:256) (Exim 4.69) (envelope-from ) id 1Or8jY-0006Sc-Np for pgsql-sql@postgresql.org; Thu, 02 Sep 2010 06:17:37 -0600 Message-ID: <4C7F95DE.9020200@negora.com> Date: Thu, 02 Sep 2010 14:17:34 +0200 From: negora User-Agent: Mozilla/5.0 (X11; U; Linux x86_64; en-US; rv:1.9.1.11) Gecko/20100713 Lightning/1.0b1 Thunderbird/3.0.6 MIME-Version: 1.0 To: pgsql-sql@postgresql.org Subject: SUM the result of a subquery. Content-Type: text/html; charset=UTF-8 Content-Transfer-Encoding: 7bit X-Identified-User: {2410:box398.bluehost.com:negoraco:negora.com} {sentby:smtp auth 80.32.234.30 authed with negora+negora.com} X-Virus-Scanned: Maia Mailguard 1.0.1 X-Spam-Status: No, hits=-1.176 tagged_above=-5 required=5 tests=BAYES_00=-1.9, HTML_MESSAGE=0.001, MIME_HTML_ONLY=0.723, RCVD_IN_DNSWL_NONE=-0.0001 X-Spam-Level: X-Archive-Number: 201009/4 X-Sequence-Number: 34535 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.
From negora@negora.com Thu Sep 2 12:26:07 2010 Received: from maia.hub.org (maia-2.hub.org [200.46.204.251]) by mail.postgresql.org (Postfix) with ESMTP id 5E385632369 for ; Thu, 2 Sep 2010 09:26:07 -0300 (ADT) Received: from mail.postgresql.org ([200.46.204.86]) by maia.hub.org (mx1.hub.org [200.46.204.251]) (amavisd-maia, port 10024) with ESMTP id 64048-07 for ; Thu, 2 Sep 2010 12:25:58 +0000 (UTC) X-Greylist: from auto-whitelisted by SQLgrey-1.7.6 Received: from oproxy3-pub.bluehost.com (oproxy3-pub.bluehost.com [69.89.21.8]) by mail.postgresql.org (Postfix) with SMTP id 58F546321C0 for ; Thu, 2 Sep 2010 09:25:59 -0300 (ADT) Received: (qmail 23008 invoked by uid 0); 2 Sep 2010 12:19:18 -0000 Received: from unknown (HELO box398.bluehost.com) (69.89.31.198) by oproxy3.bluehost.com with SMTP; 2 Sep 2010 12:19:18 -0000 DomainKey-Signature: a=rsa-sha1; q=dns; c=nofws; s=default; d=negora.com; h=Received:Message-ID:Date:From:User-Agent:MIME-Version:To:Subject:Content-Type:Content-Transfer-Encoding:X-Identified-User; b=XVPsVfjhVFCz53hvYDxB4pRLOEtZEWQN75OUvKx9gSnxETyQK3SyPruXiVaLTG2/d1sWt8XSKZ1EoCObRq/GL+6hMLVbhiVA2C89S4plc503frtObRdVZlRO/vCb7Dzb; Received: from 30.red-80-32-234.staticip.rima-tde.net ([80.32.234.30] helo=[172.16.0.2]) by box398.bluehost.com with esmtpsa (TLSv1:AES256-SHA:256) (Exim 4.69) (envelope-from ) id 1Or8lC-0007EP-EM for pgsql-sql@postgresql.org; Thu, 02 Sep 2010 06:19:18 -0600 Message-ID: <4C7F9643.7090200@negora.com> Date: Thu, 02 Sep 2010 14:19:15 +0200 From: negora User-Agent: Mozilla/5.0 (X11; U; Linux x86_64; en-US; rv:1.9.1.11) Gecko/20100713 Lightning/1.0b1 Thunderbird/3.0.6 MIME-Version: 1.0 To: pgsql-sql@postgresql.org Subject: SUM the result of a subquery. Content-Type: text/html; charset=ISO-8859-1 Content-Transfer-Encoding: 7bit X-Identified-User: {2410:box398.bluehost.com:negoraco:negora.com} {sentby:smtp auth 80.32.234.30 authed with negora+negora.com} X-Virus-Scanned: Maia Mailguard 1.0.1 X-Spam-Status: No, hits=-1.176 tagged_above=-5 required=5 tests=BAYES_00=-1.9, HTML_MESSAGE=0.001, MIME_HTML_ONLY=0.723, RCVD_IN_DNSWL_NONE=-0.0001 X-Spam-Level: X-Archive-Number: 201009/5 X-Sequence-Number: 34536 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;

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';

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.

From Jayadevan.Maymala@ibsplc.com Thu Sep 2 12:30:55 2010 Received: from maia.hub.org (maia-3.hub.org [200.46.204.243]) by mail.postgresql.org (Postfix) with ESMTP id 3C9D8632957 for ; Thu, 2 Sep 2010 09:30:55 -0300 (ADT) Received: from mail.postgresql.org ([200.46.204.86]) by maia.hub.org (mx1.hub.org [200.46.204.243]) (amavisd-maia, port 10024) with ESMTP id 66573-09; Thu, 2 Sep 2010 12:30:46 +0000 (UTC) X-Greylist: from auto-whitelisted by SQLgrey-1.7.6 X-Greylist: from auto-whitelisted by SQLgrey-1.7.6 Received: from mx1.ibsplc.com (mx1.ibsplc.com [202.177.44.5]) by mail.postgresql.org (Postfix) with ESMTP id 2CA686321C0; Thu, 2 Sep 2010 09:30:46 -0300 (ADT) X-IronPort-AV: E=Sophos;i="4.56,307,1280687400"; d="scan'208";a="17887973" Received: from unknown (HELO Mail1.ibsplc.com) ([192.168.0.8]) by mx2.ibsplc.com with ESMTP; 02 Sep 2010 17:53:52 +0530 In-Reply-To: <4C7F95DE.9020200@negora.com> References: <4C7F95DE.9020200@negora.com> X-Disclaimed: 6474 To: negora Cc: pgsql-sql@postgresql.org, pgsql-sql-owner@postgresql.org MIME-Version: 1.0 Subject: Re: SUM the result of a subquery. X-KeepSent: 39AE7609:CC04F696-65257792:00446E7D; type=4; name=$KeepSent X-Mailer: Lotus Notes Release 8.5 December 05, 2008 Message-ID: From: Jayadevan M Date: Thu, 2 Sep 2010 17:58:34 +0530 X-MIMETrack: Serialize by Router on Mail1/IBS(Release 8.5.1FP1|January 05, 2010) at 09/02/2010 05:58:39 PM, Serialize complete at 09/02/2010 05:58:39 PM Content-Type: text/plain; charset="US-ASCII" X-Virus-Scanned: Maia Mailguard 1.0.1 X-Spam-Status: No, hits=0.79 tagged_above=-5 required=5 tests=BAYES_50=0.8, T_RP_MATCHES_RCVD=-0.01 X-Spam-Level: X-Archive-Number: 201009/6 X-Sequence-Number: 34537 > 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." From negora@negora.com Thu Sep 2 12:36:12 2010 Received: from maia.hub.org (maia-2.hub.org [200.46.204.251]) by mail.postgresql.org (Postfix) with ESMTP id B5470632369 for ; Thu, 2 Sep 2010 09:36:12 -0300 (ADT) Received: from mail.postgresql.org ([200.46.204.86]) by maia.hub.org (mx1.hub.org [200.46.204.251]) (amavisd-maia, port 10024) with ESMTP id 33505-08 for ; Thu, 2 Sep 2010 12:36:04 +0000 (UTC) X-Greylist: from auto-whitelisted by SQLgrey-1.7.6 Received: from oproxy3-pub.bluehost.com (oproxy3-pub.bluehost.com [69.89.21.8]) by mail.postgresql.org (Postfix) with SMTP id 572986321C0 for ; Thu, 2 Sep 2010 09:36:05 -0300 (ADT) Received: (qmail 23064 invoked by uid 0); 2 Sep 2010 12:36:03 -0000 Received: from unknown (HELO box398.bluehost.com) (69.89.31.198) by oproxy3.bluehost.com with SMTP; 2 Sep 2010 12:36:03 -0000 DomainKey-Signature: a=rsa-sha1; q=dns; c=nofws; s=default; d=negora.com; h=Received:Message-ID:Date:From:User-Agent:MIME-Version:To:CC:Subject:References:In-Reply-To:Content-Type:Content-Transfer-Encoding:X-Identified-User; b=Fj+E8fuN81Ur9MWdhy38ZqXIZuK8v8n38MZjcOSUv8xbddxI5VTyufKDJQhwM3TDS8GekJwNPIY9Lf5ByAv+hgIxMp9HLi5wHkjFDPJ3WH/qRuv9qtOcrXl8KMNrI9sV; Received: from 30.red-80-32-234.staticip.rima-tde.net ([80.32.234.30] helo=[172.16.0.2]) by box398.bluehost.com with esmtpsa (TLSv1:AES256-SHA:256) (Exim 4.69) (envelope-from ) id 1Or91P-0007qa-FZ; Thu, 02 Sep 2010 06:36:03 -0600 Message-ID: <4C7F9A30.2010908@negora.com> Date: Thu, 02 Sep 2010 14:36:00 +0200 From: negora User-Agent: Mozilla/5.0 (X11; U; Linux x86_64; en-US; rv:1.9.1.11) Gecko/20100713 Lightning/1.0b1 Thunderbird/3.0.6 MIME-Version: 1.0 To: Jayadevan M CC: pgsql-sql@postgresql.org Subject: Re: SUM the result of a subquery. References: <4C7F95DE.9020200@negora.com> In-Reply-To: Content-Type: text/html; charset=ISO-8859-1 Content-Transfer-Encoding: 7bit X-Identified-User: {2410:box398.bluehost.com:negoraco:negora.com} {sentby:smtp auth 80.32.234.30 authed with negora+negora.com} X-Virus-Scanned: Maia Mailguard 1.0.1 X-Spam-Status: No, hits=-1.176 tagged_above=-5 required=5 tests=BAYES_00=-1.9, HTML_MESSAGE=0.001, MIME_HTML_ONLY=0.723, RCVD_IN_DNSWL_NONE=-0.0001 X-Spam-Level: X-Archive-Number: 201009/7 X-Sequence-Number: 34538 Wow, I had no idea about this kind of SELECT expression. It works flawless!!! Thank you lots Jayadevan :) .

On 02/09/10 14:28, Jayadevan M wrote:

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."