Geek asks and answers

How to use a group using wildcards in Postgres

I am having problems with this code in Postgres. I would like to get all the fields in the user table, but only group by id. This works well on MySQL and SQLite, but after googling, I found that this behavior is not 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

(Note that it <shop_id>is passed in programmatically, so you can simply replace it with any integer.)

This is the error I get

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

I am new to SQL, so be careful if this is obvious :)

+3
source share
5 answers

, , , user userprofile. ( , , , !) MySQL "" , , GROUP BY , . PostgreSQL .

. . , :

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
+3

row_number, :

select  *
from    (
        select  row_number() over (partition by u.id) as rn
        ,       *
        join    user u
        on      sub.user_id = u.id
        join    shop_order so
        on      so.user_id = u.id
        left join
                userprofile up
        on      up.user_id = u.id
        where   so.shop_id = <shop_id>
        ) as SubQueryAlias
where   rn = 1

, Postgres , row_number where.

+2

- , , , , , u.id, " , "

+2

Postgres? Postgres 9.0 , . :

SELECT
    p.id,
    p.firstname, p.lastname, p.address, -- these auxiliary fields is helpful to program users
    count(*)
FROM
    people p
    JOIN visits v ON p.id = v.person_id
GROUP BY p.id

9.0 :

SELECT
    p.id,
    p.firstname, p.lastname, p.address, -- these auxiliary fields is helpful to program users
    count(*)
FROM
    people p
    JOIN visits v ON p.id = v.person_id
GROUP BY p.id
    ,p.firstname, p.lastname, p.address; -- these ancillary fields aids the RDBMS on preventing
    -- programmers from accidentally committing the same mistake as MySQL programmers.

Postgres 9.1, GROUP BY, SELECT, GROUPed . , :

SELECT
    p.id,
    p.firstname, p.lastname, p.address, -- these auxiliary fields is helpful to program users
    count(*)
FROM
    people p
    JOIN visits v ON p.id = v.person_id
GROUP BY p.id 
-- note that there no need to repeat the SELECTed fields on GROUP BY clause

Postgres 9.1 , GROUP BY

SQL Fiddle: http://sqlfiddle.com/#!1/3b857/3

+2

PostgreSQL ? , 9.1.

9.1 PostgreSQL , , GROUP BY . , , .

+1

More articles:


All Articles