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,
CREATE TABLE license(
person_id integer REFERENCES person(id),
CREATE TABLE car(
owner_id integer REFERENCES person(id),
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.