0

I am performing a pretty simple query in PostGIS 2.1.1 from within QGIS 2.2 for Windows (using the DB Manager plugin). I have a table with many columns and I am trying to perform a Group By function. However, PostGIS returns an error when I do not add all the columns from my SELECT call within the GROUP BY call (see below).

ORIGINAL QUERY

SELECT city, state, city_abbr
FROM "public".footprints
WHERE city_abbr = 'nyc'
GROUP BY city

This query returns the following error:

column "footprints.state" must appear in the GROUP BY clause or be used in an aggregate      function
LINE 1: SELECT city, state, city_abbr

If I change my query to the following, it runs.

ALTERED QUERY

SELECT city, state, city_abbr
FROM "public".footprints
WHERE city_abbr = 'nyc'
GROUP BY city, state, city_abbr

I do not want to have to add each column to my GROUP BY statement. Can anyone shed any light on why this would be happening.

Thanks...

EDIT

Here is an example of the data in the table

gid   | city      | state   | city_abbr
______________________________________
0        boston      ny         bos
1        manhattan   ny         nyc
2        brooklyn    ny         nyc
3        newark      nj         nyc
4        denver      co         den
5        seattle     wa         sea        

closed as off-topic by underdark Jun 16 '14 at 20:20

This question appears to be off-topic. The users who voted to close gave this specific reason:

  • "Questions about software development are off-topic here unless they relate directly to Geographical Information Systems, but they can be asked on Stack Overflow." – underdark
If this question can be reworded to fit the rules in the help center, please edit the question.

  • 1
    What do you want to calculate per-city? Your current queries are basically just no-ops. "Tell me every city." Is that what you want? "SELECT DISTINCT city FROM footprints" is a more correct way to get that. – Paul Ramsey Jun 16 '14 at 20:10
  • It would help if you described the columns and their contents, so we wouldn't have to guess what they might contain. – Vince Jun 16 '14 at 20:12
  • When I perform a similar query in Spatialite I do not see this issue. What I am looking for is all the city names that have a city_abbr of nyc. I want to make sure I only get one return if there are cities that share the same city name. I understand distinct will give me this result, but I don't understand why GROUP BY needs to have all the columns that are identified in the SELECT. – Ryan Garnett Jun 16 '14 at 20:13
  • Seems like a general Postgres question to me rather than anything PostGIS or QGIS specific. – underdark Jun 16 '14 at 20:22
  • 1
    Right, SQLite's loosey goosey behavior in this case is a bug, not a feature. – Paul Ramsey Jun 16 '14 at 20:26
0

See this answer from Stack Overflow, in particular the first comment, which states

Postgres 9.1 allows to not list all columns if their table's primary key is part of the group by clause

So your query could be changed to (assuming primary key is uid):

SELECT city, state, city_abbr
FROM "public".footprints
WHERE city_abbr = 'nyc'
GROUP BY city,uid

And I believe it shouldn't balk at you.

  • Thanks, that worked. Do you have any idea why it would need a uid / gid? I haven't needed to do this with other databases. – Ryan Garnett Jun 16 '14 at 20:22
  • You'll also get one row per footprint, which hardly seems like the answer you're looking for. – Paul Ramsey Jun 16 '14 at 20:28
  • I feel also that you are misusing GROUG BY as Paul R. said. – user30184 Jun 16 '14 at 20:29

Not the answer you're looking for? Browse other questions tagged or ask your own question.