pg.ddx.io pgsql-sql@postgresql.org mailing list archive
help / color / mirror / Atom feedNested select
5+ messages / 5 participants
[nested] [flat]
* Nested select
@ 2006-11-06 11:08 Hubert Retif <hubert.retif@i-netsoft.ch>
2006-11-06 19:07 ` Re: Nested select imad <immaad@gmail.com>
2006-11-06 19:21 ` Re: Nested select Tom Lane <tgl@sss.pgh.pa.us>
2006-11-07 14:40 ` Re: Nested select Aaron Bono <postgresql@aranya.com>
2006-11-07 18:03 ` Re: Nested select Scott Marlowe <smarlowe@g2switchworks.com>
0 siblings, 4 replies; 5+ messages in thread
From: Hubert Retif @ 2006-11-06 11:08 UTC (permalink / raw)
To: pgsql-sql
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
^ permalink raw reply [nested|flat] 5+ messages in thread
* Re: Nested select
2006-11-06 11:08 Nested select Hubert Retif <hubert.retif@i-netsoft.ch>
@ 2006-11-06 19:07 ` imad <immaad@gmail.com>
3 siblings, 0 replies; 5+ messages in thread
From: imad @ 2006-11-06 19:07 UTC (permalink / raw)
To: hubert.retif@i-netsoft.ch; +Cc: pgsql-sql
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
^ permalink raw reply [nested|flat] 5+ messages in thread
* Re: Nested select
2006-11-06 11:08 Nested select Hubert Retif <hubert.retif@i-netsoft.ch>
@ 2006-11-06 19:21 ` Tom Lane <tgl@sss.pgh.pa.us>
3 siblings, 0 replies; 5+ messages in thread
From: Tom Lane @ 2006-11-06 19:21 UTC (permalink / raw)
To: hubert.retif@i-netsoft.ch; +Cc: pgsql-sql
=?us-ascii?Q?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
^ permalink raw reply [nested|flat] 5+ messages in thread
* Re: Nested select
2006-11-06 11:08 Nested select Hubert Retif <hubert.retif@i-netsoft.ch>
@ 2006-11-07 14:40 ` Aaron Bono <postgresql@aranya.com>
3 siblings, 0 replies; 5+ messages in thread
From: Aaron Bono @ 2006-11-07 14:40 UTC (permalink / raw)
To: hubert.retif@i-netsoft.ch; +Cc: pgsql-sql
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
==================================================================
^ permalink raw reply [nested|flat] 5+ messages in thread
* Re: Nested select
2006-11-06 11:08 Nested select Hubert Retif <hubert.retif@i-netsoft.ch>
@ 2006-11-07 18:03 ` Scott Marlowe <smarlowe@g2switchworks.com>
3 siblings, 0 replies; 5+ messages in thread
From: Scott Marlowe @ 2006-11-07 18:03 UTC (permalink / raw)
To: hubert.retif@i-netsoft.ch; +Cc: pgsql-sql
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.
^ permalink raw reply [nested|flat] 5+ messages in thread
end of thread, other threads:[~2006-11-07 18:03 UTC | newest]
Thread overview: 5+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2006-11-06 11:08 Nested select Hubert Retif <hubert.retif@i-netsoft.ch>
2006-11-06 19:07 ` imad <immaad@gmail.com>
2006-11-06 19:21 ` Tom Lane <tgl@sss.pgh.pa.us>
2006-11-07 14:40 ` Aaron Bono <postgresql@aranya.com>
2006-11-07 18:03 ` Scott Marlowe <smarlowe@g2switchworks.com>
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox