Received: from maia.hub.org (maia-2.hub.org [200.46.204.251])
	by mail.postgresql.org (Postfix) with ESMTP id B5470632369
	for <pgsql-sql-postgresql.org@mail.postgresql.org>;
	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
	<pgsql-sql-postgresql.org@mail.postgresql.org>;
	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 <pgsql-sql@postgresql.org>; 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 <negora@negora.com>)
	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 <negora@negora.com>
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 <Jayadevan.Maymala@ibsplc.com>
CC: pgsql-sql@postgresql.org
Subject: Re: SUM the result of a subquery.
References: <4C7F95DE.9020200@negora.com>
	<OF39AE7609.CC04F696-ON65257792.00446E7D-65257792.00448A26@ibsplc.com>
In-Reply-To: 
 <OF39AE7609.CC04F696-ON65257792.00446E7D-65257792.00448A26@ibsplc.com>
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

<!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>