How to use a group by with wildcards in Postgres

Question

I'm having trouble with this code in Postgres. I'd like to get all fields in the user table, but only group by id. This works well on MySQL and SQLite, but after googling I've found out that this behaviour isn't part of the standard.

SELECT u.*, p.*, count(o.id) AS order_count
FROM shop_order AS o
LEFT JOIN user AS u ON o.user_id = u.id
LEFT JOIN userprofile AS p ON p.user_id = u.id
WHERE o.shop_id = <shop_id>
GROUP BY u.id

(Please note that <shop_id> is being passed programmatically, so you could just as easily replace it with any integer.)

This is the error I'm getting

column "u.id" must appear in the GROUP BY clause or be used in an aggregate function

I'm an SQL newbie, so be gentle if this is obvious :)

Answer 1

Unfortunately I don't think you can do it without listing all the columns in user and userprofile. (Use of wildcards is often considered to be something you do for simple ad-hoc queries only, and I guess this is one of the reasons!) MySQL and other DBs have a "convenient" feature that if a column is neither in the GROUP BY clause nor in an aggregate function, then any of the values for that column in each group can be shown in the result. PostgreSQL is a bit stricter.

But normally, only a few columns of the table are sufficient for the grouping. There are lots of ways to rewrite it. Perhaps what you can do instead is:

SELECT u.*, p.*, (select count(o.id) FROM shop_order AS o WHERE o.user_id = u.id AND o.shop_id = <shop_id>) AS order_count
FROM user AS u
LEFT JOIN userprofile AS p ON p.user_id = u.id