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).
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.
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.
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