Re: Nested select

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.