Postgresql usage of aggregates and non-aggregate columns, grouping by

Question

Some things in lecture and in my lab assignment were not explained very well. I am having trouble displaying the correct information. Here is the database info, simply a reference for you to help me. The database tables info This is the query that I am trying to execute The postgresql php select statement

This results in this SQl error being throwned

Connected to database! Query failed: ERROR: column "city.name" must appear in the GROUP BY clause or be used in an aggregate function LINE 3: city.name ^

Now if I do add city.name into the GROUP BY clause, it returns 4096 rows! I dont want that to happen, the results have to be group by country name which is 232 rows. I simply want to display the country name, city name, and the city with the highest population in that country. City name is throwing me off, Im guessing there is a more complicated more syntax heavy solution. Thanks for any help you can provide. -Tom Reese

Lajos Veres · Accepted Answer · 2013-10-06 18:55:27Z

1

You need something like this:

select
        country.name,
        city.name,
        mp.maxpop
from
        lab6.country,
        lab6.city,
        (select
                country_code,
                max(population) as maxpop
        from
                lab6.city
        group by
                country_code
        ) mp
where
        country.country_code=mp.country_code and
        country.country_code=city.county_code and 
        mp.maxpop=city.population

notes:

This can give you more result/county.
Your original query doesn't work because in ansi sql you can't return only aggregated or group by expressions from a "group by" query. (As the error mentions)

edited Oct 6, 2013 at 18:55

answered Oct 6, 2013 at 18:35

Lajos Veres

13.7k7 gold badges47 silver badges57 bronze badges

Sign up to request clarification or add additional context in comments.

5 Comments

tom reese Over a year ago

ok this makes more sense the professor did provide a example that was very similar to this but it was only on one table, i could not connect the dots. Thanks Lajos

Lajos Veres Over a year ago

welcome. There are other methods to achieve the same result, but they are not ansi sql: postgresql.org/docs/8.4/static/tutorial-window.html In school probably you should practice the learned methods.

mu is too short Over a year ago

I'm pretty sure window functions are standard SQL. I think your first where is supposed to be a from and you might want to do something about those nasty implicit joins.

Lajos Veres Over a year ago

You are right... When I learnt sql these window functions not existed in practice... :-)

tom reese Over a year ago

yeah i fixed the where to a from, every thing is working, here is proof imgur.com/gS1mwmf

Collectives™ on Stack Overflow

Postgresql usage of aggregates and non-aggregate columns, grouping by

1 Answer 1

5 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

5 Comments

Your Answer

Sign up or log in

Post as a guest

Related