Thread: Nested select
<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>
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
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
On 11/6/06, Hubert Retif <hubert.retif@i-netsoft.ch> wrote:
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
==================================================================
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
==================================================================
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.