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;

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.