Postgresql – Getting around the constraint “column must appear in the GROUP BY clause or be used in an aggregate function”

aggregategroup byjoin;postgresql

I'm using Postgres, which enforces the constraint that all columns in a SELECT…GROUP BY must appear in the GROUP BY clause or be used in an aggregate function.

Lets say I'm modelling peoples' cars, and I want to work out a person's name, license number, and how many cars they have. Here's my example as an SQL Fiddle.

I would have the following schema:

CREATE TABLE person(
  id integer PRIMARY KEY,
  name text
);

CREATE TABLE license(
  person_id integer REFERENCES person(id),
  expiry_date date
);

CREATE TABLE car(
  owner_id integer REFERENCES person(id),
  registration_number TEXT
);

Here's the query:

SELECT person.name, person.id, license.expiry_date, COUNT(car) FROM person
  JOIN license ON license.person_id = person.id
  JOIN car ON car.owner_id = person.id
WHERE person.name = 'Charles Bannerman'
GROUP BY person.id;

I know, because of my own business logic, that one person can only have one license, so when I join to the person, even though it's GROUP BY'd, I should be able to find their license number. However because it isn't part of the GROUP BY statement and doesn't use an aggregate function, I can't access it.

How should I write this query in an idiomatic way. I've seen vague things about LATERAL JOINs, window functions, and subqueries, but I'm not sure the best way to answer this question.

Best Answer

seharusnya seperti ini(supposed to be like this):

SELECT person.name, person.id, license.expiry_date, COUNT(car) FROM person
  JOIN license ON license.person_id = person.id
  JOIN car ON car.owner_id = person.id
WHERE person.name = 'Charles Bannerman'
GROUP BY person.name, person.id, license.expiry_date, car.car;

Related Solutions

Postgresql – Postgres error [column must appear in the GROUP BY clause or be used in an aggregate function] when sub query is used

The feature of Postgres to be able to use the primary key of a table with GROUP BY and not need to add the other columns of that table in the GROUP BY clause is relatively new and works only for base tables. The optimizer is not (yet?) clever enough to identify primary keys for views, ctes or derived tables (as in your case).

You can add the columns you want in the SELECT into the GROUP BY clause:

SELECT e.empname, array_agg(p.phonenumber) AS phonenumbers 
FROM 
(SELECT * FROM employee ORDER BY empname LIMIT 3 OFFSET 0) AS e 
LEFT OUTER JOIN phones AS p ON e.empid = p.empid
GROUP BY e.empid, e.empname 
ORDER BY e.empname ;

or use a subquery (and transfer the GROUP BY there):

SELECT e.empname,
       (SELECT array_agg(p.phonenumber) 
        FROM phones AS p
        WHERE e.empid = p.empid
       ) AS phonenumbers 
FROM 
(SELECT * FROM employee ORDER BY empname LIMIT 3 OFFSET 0) AS e 
ORDER BY e.empname ;

which could also be written as:

SELECT e.empname,
       (SELECT array_agg(p.phonenumber) 
        FROM phones AS p
        WHERE e.empid = p.empid
       ) AS phonenumbers 
FROM employee AS e
ORDER BY e.empname LIMIT 3 OFFSET 0 ;

Since you are in version 9.3+. you can also use a LATERAL join:

SELECT e.empname,
       p.phonenumbers 
FROM 
   (SELECT * FROM employee ORDER BY empname LIMIT 3 OFFSET 0) AS e
LEFT JOIN LATERAL
   (SELECT array_agg(phonenumber) AS phonenumbers
    FROM phones 
    WHERE e.empid = phones.empid
   ) AS p ON TRUE 
ORDER BY e.empname ;

Postgresql – Column must appear in the GROUP BY clause error even though it DOES appear there

I figured it out. It's the damn parentheses around the columns in the GROUP BY clause. The correct query is:

select order_number,key_a,key_b,key_c,sum(quantity) from my_table
      group by order_number,key_a,key_b,key_c;

Best Answer

Related Solutions

Postgresql – Postgres error [column must appear in the GROUP BY clause or be used in an aggregate function] when sub query is used

Postgresql – Column must appear in the GROUP BY clause error even though it DOES appear there

Related Question