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.