Getting around the constraint "column must appear in the GROUP BY clause or be used in an aggregate function"

Question

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.

Answer 1

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;

Answer 2

You could GROUP BY before you do any joining... Which means, reverse your query.

This is your OLD 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;

This is your NEW query

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

That solution may not be as efficient as you want... Another potential solution would be to write a plpgsql function to return the count of cars when given a person_id..

The solution I would choose would depend on the use case of the query.. Will the query ALWAYS be used to return a single row of data (for a specific person)?.. Or will it be used in a report to display many rows?..

Answer 3

SELECT person.name, 
       person.id, 
       license.expiry_date, COUNT(car) OVER (Partition by Person.Id)
FROM person
  JOIN license ON license.person_id = person.id
  JOIN car ON car.owner_id = person.id
WHERE person.name = 'Charles Bannerman';

You can use OVER (partion by <grouping column(s)>). This will aggregate the data with out having to use a Group by.

The only drawback to this is if there are duplicate Person.id and license.expiry_date you will get 2 results. But you can add any other data without having it be part of the group by. Just a thought.