[ACCEPTED]-Why no "SELECT foo.* ... GROUP BY foo.id" in Postgres?-postgresql

Just in case other people stumble over this 4 question:

Starting with PostgreSQL 9.1 it's 3 sufficient to list the columns of the primary 2 key in the group by clause (so the example 1 from the question would work now).

Some databases are more relaxed about this, for 15 good and bad. The query is unspecific, so 14 the result is equally unspecific. If the 13 database allows the query, it will return 12 one record from each group and it won't 11 care which one. Other databases are more 10 specific, and require you to specify which 9 value you want from the group. They won't 8 let you write a query that has an unspecific 7 result.

The only values that you can select 6 without an aggregate is the ones in the 5 group by clause:

select foo.id, count(bar.id)
from foo inner join bar on foo.id = bar.foo_id
group by foo.id

You can use aggregates to get other 4 values:

select foo.id, min(foo.price), count(bar.id)
from foo inner join bar on foo.id = bar.foo_id
group by foo.id

If you want all the values from the 3 foo table, you can either put them all in 2 the group by clause (if that gives the correct result):

select foo.id, foo.price, foo.name, foo.address, count(bar.id)
from foo inner join bar on foo.id = bar.foo_id
group by foo.id, foo.price, foo.name, foo.address

Or, you 1 can join the table with a subquery:

select foo.id, foo.price, foo.name, foo.address, sub.bar_count
from foo
inner join (
   select foo.id, bar_count = count(bar.id)
   from foo inner join bar on foo.id = bar.foo_id
   group by foo.id
) sub on sub.id = foo.id

What exactly would you have postgresql output? You're 18 using an aggregate function and trying to 17 output "something".

Ah. I see what you may 16 want to do. Use a subselect.

select foo.*, (select count(*) from bar where bar.foo_id=foo.id) from foo;

Check with explain 15 that the plan looks good though. A subselect 14 is not always bad. I just checked with a 13 database I'm using and my execution plan 12 was good for that query.

Yes, in theory grouping 11 by foo.id would be enough (i.e.: your query 10 plus "group by foo.id"). But apparently 9 (I tested it) postgresql will not do that. The 8 other option is to "group by foo.id, foo.foo, foo.bar, foo.baz" and 7 everything else that's in "foo.*".

Another 6 way, that Guffa is on to, is this:

SELECT foo.*, COALESCE(sub.cnt, 0)
FROM foo
LEFT OUTER JOIN (
  SELECT foo_id, count(*) AS cnt
  FROM bar
  GROUP BY foo_id) sub
ON sub.foo_id = foo.id;

This will 5 be two queries though (one subquery, which 4 is run just once), which can matter, but 3 probably won't. If you can just do without 2 "foo.*" you can use the second version that 1 explicitly groups by all columns.

A GROUP BY clause requires that every column that 15 the query returns is either a column contained 14 in the GROUP BY statement or an aggregate function 13 (such as the COUNT in your example). Without 12 seeing what your GROUP BY clause is or what the 11 columns of foo are, it's hard to tell what 10 exactly is going on, but I would guess the 9 problem is that foo.* is trying to return one 8 or several columns that are not in your 7 GROUP BY clause.

This is really a general property 6 of SQL and should not be specific to PostgreSQL. No 5 idea why it worked for you with SQLite or 4 MySQL -- perhaps all the columns in foo.* are 3 actually in your GROUP BY clause but PostgreSQL 2 can't figure that out -- so try listing 1 out all of the columns of foo explicitly.