Thread: Nested select

Nested select

From
Hubert Retif
Date:
<div class="Section1"><p class="MsoNormal"><font face="Arial" size="2"><span lang="DE-CH" style="font-size:
10.0pt;font-family:Arial">Hi,</span></font><p class="MsoNormal"><font face="Arial" size="2"><span lang="DE-CH"
style="font-size:
10.0pt;font-family:Arial"> </span></font><p class="MsoNormal"><font face="Arial" size="2"><span lang="EN-GB"
style="font-size:
10.0pt;font-family:Arial">I am migrating my application from MySQL to Postgresql and have met following
situation:</span></font><pclass="MsoNormal"><font face="Arial" size="2"><span lang="EN-GB" style="font-size: 
10.0pt;font-family:Arial"> </span></font><p class="MsoNormal"><font face="Arial" size="2"><span lang="EN-GB"
style="font-size:
10.0pt;font-family:Arial">SELECT</span></font><p class="MsoNormal"><font face="Arial" size="2"><span lang="EN-GB"
style="font-size:
10.0pt;font-family:Arial">(sum(sold_price)/(select sum(sold_price) from car_archive))*100 as CA_pcent,</span></font><p
class="MsoNormal"><fontface="Arial" size="2"><span lang="EN-GB" style="font-size: 
10.0pt;font-family:Arial">reason_text</span></font><p class="MsoNormal"><font face="Arial" size="2"><span lang="EN-GB"
style="font-size:
10.0pt;font-family:Arial">FROM car_archive</span></font><p class="MsoNormal"><font face="Arial" size="2"><span
lang="EN-GB"style="font-size: 
10.0pt;font-family:Arial">group by reason_text</span></font><p class="MsoNormal"><font face="Arial" size="2"><span
lang="EN-GB"style="font-size: 
10.0pt;font-family:Arial">order by CA_pcent desc</span></font><p class="MsoNormal"><font face="Arial" size="2"><span
lang="EN-GB"style="font-size: 
10.0pt;font-family:Arial"> </span></font><p class="MsoNormal"><font face="Arial" size="2"><span lang="EN-GB"
style="font-size:
10.0pt;font-family:Arial">works perfectly in MySQL, but not in Postgresql. The problem seems to be the nested select
whichshould deliver a sum of the whole table, but it does not.</span></font><p class="MsoNormal"><font face="Arial"
size="2"><spanlang="EN-GB" style="font-size: 
10.0pt;font-family:Arial"> </span></font><p class="MsoNormal"><font face="Arial" size="2"><span lang="EN-GB"
style="font-size:
10.0pt;font-family:Arial">If I replace this nested select with a fix value, then it works:</span></font><p
class="MsoNormal"><fontface="Arial" size="2"><span lang="EN-GB" style="font-size: 
10.0pt;font-family:Arial"> </span></font><p class="MsoNormal"><font face="Arial" size="2"><span lang="EN-GB"
style="font-size:
10.0pt;font-family:Arial">SELECT</span></font><p class="MsoNormal"><font face="Arial" size="2"><span lang="EN-GB"
style="font-size:
10.0pt;font-family:Arial">(sum(sold_price)/(333))*100 as CA_pcent,</span></font><p class="MsoNormal"><font face="Arial"
size="2"><spanlang="EN-GB" style="font-size: 
10.0pt;font-family:Arial">reason_text</span></font><p class="MsoNormal"><font face="Arial" size="2"><span lang="EN-GB"
style="font-size:
10.0pt;font-family:Arial">FROM car_archive</span></font><p class="MsoNormal"><font face="Arial" size="2"><span
lang="EN-GB"style="font-size: 
10.0pt;font-family:Arial">group by reason_text</span></font><p class="MsoNormal"><font face="Arial" size="2"><span
lang="EN-GB"style="font-size: 
10.0pt;font-family:Arial">order by CA_pcent desc</span></font><p class="MsoNormal"><font face="Arial" size="2"><span
lang="EN-GB"style="font-size: 
10.0pt;font-family:Arial"> </span></font><p class="MsoNormal"><font face="Arial" size="2"><span lang="EN-GB"
style="font-size:
10.0pt;font-family:Arial">Has someone a solution for that situation?</span></font><p class="MsoNormal"><font
face="Arial"size="2"><span lang="EN-GB" style="font-size: 
10.0pt;font-family:Arial"> </span></font><p class="MsoNormal"><font face="Arial" size="2"><span lang="EN-GB"
style="font-size:
10.0pt;font-family:Arial">Thanks,</span></font><p class="MsoNormal"><font face="Arial" size="2"><span lang="EN-GB"
style="font-size:
10.0pt;font-family:Arial">Hubert Retif</span></font></div>

Re: Nested select

From
imad
Date:
Can you write the error message here?

--Imad
www.EnterpriseDB.com


On 11/6/06, Hubert Retif <hubert.retif@i-netsoft.ch> wrote:
>
>
>
>
> Hi,
>
>
>
> 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
>
>
>
> Has someone a solution for that situation?
>
>
>
> Thanks,
>
> Hubert Retif


Re: Nested select

From
Tom Lane
Date:
Hubert Retif <hubert.retif@i-netsoft.ch> writes:
> I am migrating my application from MySQL to Postgresql and have met
> following situation:
> ...
> works perfectly in MySQL, but not in Postgresql.

If you want useful help, you need to explain exactly what results you
got and what you expected to get.  "Works perfectly" is content-free.
        regards, tom lane


Re: Nested select

From
"Aaron Bono"
Date:
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
==================================================================

Re: Nested select

From
Scott Marlowe
Date:
On Mon, 2006-11-06 at 05:08, Hubert Retif wrote:
> Hi,
> 
>  
> 
> 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.

Yes, I'm pretty sure that I know what the problem is here.  It's that
MySQL is sloppy about group by and select lists, and PostgreSQL is
pretty exacting about them.

In MySQL, this will work:

table_1
col1 | col2
1 | 2
1 | 3

select col1, col2 from table_1 group by col1

and you'll get back either (1,2) or (1,3) but you really can't be sure
which one.

In PostgreSQL, that table and that query would get an error, because any
column in the select list must be in the group by.  The SQL spec allows
for certain other situations where you'd be guaranteed for col2 to be
unique, but postgresql isn't programmed for them.

So, with your query, (select sum(sold_price) from car_archive)) isn't in
the group by, so it's illegal.  You should be able to use a subselect to
do a workaround, and as an added bonus, said subselect should work on
other SQL compliant databases as well, not just postgresql. 

select    (sold_price/sum(sold_price))*100 as pct,    reason_text 
from car_archive

gets us a list of all the sold_prices/sum as pct, and the reasons... 
then, we subselect on that and use group by to get what we want.

select a.pct, a.reason_text 
from (   select        (sold_price/sum(sold_price))*100 as pct,        reason_text    from car_archive   ) as a
group by a.reason_text, a.pct

that might work.  or be close.  no guarantees, guaranteed void in
Tennessee.