Received: from localhost (mx1.hub.org [200.46.208.251])
	by postgresql.org (Postfix) with ESMTP id 069859FA23F
	for <pgsql-sql-postgresql.org@postgresql.org>;
	Tue,  7 Nov 2006 10:40:51 -0400 (AST)
Received: from postgresql.org ([200.46.204.71])
	by localhost (mx1.hub.org [200.46.208.251]) (amavisd-new, port 10024)
	with ESMTP id 81567-04 for <pgsql-sql-postgresql.org@postgresql.org>;
	Tue,  7 Nov 2006 10:40:42 -0400 (AST)
X-Greylist: domain auto-whitelisted by SQLgrey-
Received: from ug-out-1314.google.com (ug-out-1314.google.com [66.249.92.175])
	by postgresql.org (Postfix) with ESMTP id 738B09FA22A
	for <pgsql-sql@postgresql.org>; Tue,  7 Nov 2006 10:40:41 -0400 (AST)
Received: by ug-out-1314.google.com with SMTP id k3so1036910ugf
	for <pgsql-sql@postgresql.org>; Tue, 07 Nov 2006 06:40:39 -0800 (PST)
DomainKey-Signature: a=rsa-sha1; q=dns; c=nofws; s=beta; d=gmail.com;
	h=received:message-id:date:from:sender:to:subject:cc:in-reply-to:mime-version:content-type:references:x-google-sender-auth;
	b=Ujkhg7IE48AQidLBP74hA6GTHJeQAS50qcAiGUnbgc+8hmIMyn+TgldZJ3vG2aai7CogDvA+zDwLNyTz6ackdG7SbuLGXsggP3GL1FywNxLdMFwmtpsxB5/Jv4nl86FxrCWuk8eI0Bp4h5KnKR4tYYEPTpNXQnwMG3CYcb0eGP4=
Received: by 10.78.164.13 with SMTP id m13mr4974833hue.1162910438682;
	Tue, 07 Nov 2006 06:40:38 -0800 (PST)
Received: by 10.78.136.6 with HTTP; Tue, 7 Nov 2006 06:40:38 -0800 (PST)
Message-ID: <bf05e51c0611070640q15f4b519jf6ababf5c439f1b0@mail.gmail.com>
Date: Tue, 7 Nov 2006 08:40:38 -0600
From: "Aaron Bono" <postgresql@aranya.com>
To: hubert.retif@i-netsoft.ch
Subject: Re: Nested select
Cc: pgsql-sql@postgresql.org
In-Reply-To: <200611061136.kA6BahZc012041@saturn11.saturn11.de>
MIME-Version: 1.0
Content-Type: multipart/alternative;
	boundary="----=_Part_5005_12421655.1162910438556"
References: <200611061136.kA6BahZc012041@saturn11.saturn11.de>
X-Google-Sender-Auth: 0dd5afaf7a7fe23c
X-Virus-Scanned: Maia Mailguard 1.0.1
X-Archive-Number: 200611/99
X-Sequence-Number: 26751

------=_Part_5005_12421655.1162910438556
Content-Type: text/plain; charset=ISO-8859-1; format=flowed
Content-Transfer-Encoding: 7bit
Content-Disposition: inline

On 11/6/06, Hubert Retif <hubert.retif@i-netsoft.ch> wrote:
>
> I am migrating my application from MySQL to Postgresql and have met
> following situation:
>
>
>
> SELECT
>
> (sum(sold_price)/(select sum(sold_price) from car_archive))*100 as
> CA_pcent,
>
> reason_text
>
> FROM car_archive
>
> group by reason_text
>
> order by CA_pcent desc
>
>
>
> works perfectly in MySQL, but not in Postgresql. The problem seems to be
> the nested select which should deliver a sum of the whole table, but it does
> not.
>
>
>
> If I replace this nested select with a fix value, then it works:
>
>
>
> SELECT
>
> (sum(sold_price)/(333))*100 as CA_pcent,
>
> reason_text
>
> FROM car_archive
>
> group by reason_text
>
> order by CA_pcent desc
>

Are you suffering from round off error?  That is the only thing I can think
of from what you have said.

What happens if you cast the price to a real or double precision?

==================================================================
   Aaron Bono
   Aranya Software Technologies, Inc.
   http://www.aranya.com
   http://codeelixir.com
==================================================================

------=_Part_5005_12421655.1162910438556
Content-Type: text/html; charset=ISO-8859-1
Content-Transfer-Encoding: 7bit
Content-Disposition: inline

On 11/6/06, <b class="gmail_sendername">Hubert Retif</b> &lt;<a href="mailto:hubert.retif@i-netsoft.ch">hubert.retif@i-netsoft.ch</a>&gt; wrote:<div><blockquote class="gmail_quote" style="border-left: 1px solid rgb(204, 204, 204); margin: 0pt 0pt 0pt 0.8ex; padding-left: 1ex;">
<div link="blue" vlink="purple" lang="DE"><div><p><font face="Arial" size="2"><span style="font-size: 10pt; font-family: Arial;" lang="EN-GB">I am migrating my application from MySQL to
Postgresql and have met following situation:</span></font></p>

<p><font face="Arial" size="2"><span style="font-size: 10pt; font-family: Arial;" lang="EN-GB">&nbsp;</span></font></p>

<p><font face="Arial" size="2"><span style="font-size: 10pt; font-family: Arial;" lang="EN-GB">SELECT</span></font></p>

<p><font face="Arial" size="2"><span style="font-size: 10pt; font-family: Arial;" lang="EN-GB">(sum(sold_price)/(select sum(sold_price) from
car_archive))*100 as CA_pcent,</span></font></p>

<p><font face="Arial" size="2"><span style="font-size: 10pt; font-family: Arial;" lang="EN-GB">reason_text</span></font></p>

<p><font face="Arial" size="2"><span style="font-size: 10pt; font-family: Arial;" lang="EN-GB">FROM car_archive</span></font></p>

<p><font face="Arial" size="2"><span style="font-size: 10pt; font-family: Arial;" lang="EN-GB">group by reason_text</span></font></p>

<p><font face="Arial" size="2"><span style="font-size: 10pt; font-family: Arial;" lang="EN-GB">order by CA_pcent desc</span></font></p>

<p><font face="Arial" size="2"><span style="font-size: 10pt; font-family: Arial;" lang="EN-GB">&nbsp;</span></font></p>

<p><font face="Arial" size="2"><span style="font-size: 10pt; font-family: Arial;" lang="EN-GB">works perfectly in MySQL, but not in Postgresql. The
problem seems to be the nested select which should deliver a sum of the whole
table, but it does not.</span></font></p>

<p><font face="Arial" size="2"><span style="font-size: 10pt; font-family: Arial;" lang="EN-GB">&nbsp;</span></font></p>

<p><font face="Arial" size="2"><span style="font-size: 10pt; font-family: Arial;" lang="EN-GB">If I replace this nested select with a fix value,
then it works:</span></font></p>

<p><font face="Arial" size="2"><span style="font-size: 10pt; font-family: Arial;" lang="EN-GB">&nbsp;</span></font></p>

<p><font face="Arial" size="2"><span style="font-size: 10pt; font-family: Arial;" lang="EN-GB">SELECT</span></font></p>

<p><font face="Arial" size="2"><span style="font-size: 10pt; font-family: Arial;" lang="EN-GB">(sum(sold_price)/(333))*100 as CA_pcent,</span></font></p>

<p><font face="Arial" size="2"><span style="font-size: 10pt; font-family: Arial;" lang="EN-GB">reason_text</span></font></p>

<p><font face="Arial" size="2"><span style="font-size: 10pt; font-family: Arial;" lang="EN-GB">FROM car_archive</span></font></p>

<p><font face="Arial" size="2"><span style="font-size: 10pt; font-family: Arial;" lang="EN-GB">group by reason_text</span></font></p>

<p><font face="Arial" size="2"><span style="font-size: 10pt; font-family: Arial;" lang="EN-GB">order by CA_pcent desc</span></font></p></div></div></blockquote></div><br>Are you suffering from round off error?&nbsp; That is the only thing I can think of from what you have said.
<br><br>What happens if you cast the price to a real or double precision?<br><br>==================================================================<br>&nbsp;&nbsp; Aaron Bono<br>&nbsp;&nbsp; Aranya Software Technologies, Inc.<br>&nbsp;&nbsp; <a href="http://www.aranya.com">
http://www.aranya.com</a><br>&nbsp;&nbsp; <a href="http://codeelixir.com">http://codeelixir.com</a><br>==================================================================

------=_Part_5005_12421655.1162910438556--