6

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.

  • 2
    Haven't really read the question, but this sort of thing often turns out to be people looking for DISTINCT ON. – Craig Ringer Jan 22 '15 at 3:08
  • If you know that there is only one person.name and license.expiry_date for each person_id, you could do SELECT MAX(person.name), person.id, MAX(license.expiry_date), COUNT(car) FROM person without losing anything in result - although it is not very clever solution. – zagrimsan Jan 22 '15 at 8:12
  • Yeah I've been doing that a lot unfortunately. It gets very messy when you need to do it to a lot of columns though – Migwell Jan 22 '15 at 8:13
  • 1
    If the license table had a primary key (why doesn't it have one?), you can use group by person.id, license.id because the ungrouped columns are then functionally dependent on the two primary keys. – a_horse_with_no_name Jan 22 '15 at 9:05
  • 1
    You state that "one person can only have one license" so why is person_id not a primary key in the license table then? – a_horse_with_no_name Jan 22 '15 at 9:37
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;
  • 1
    Grouping by car.car is superfluous here since it is aggregated by COUNT. – zagrimsan Jan 23 '15 at 8:34
  • I'm not sure I agree with this approach. Using a GROUP BY on every column is slower, less maintainable, and can create more groups than necessary because I only want to group on person.id. rpbouman.blogspot.com.au/2007/05/debunking-group-by-myths.html – Migwell Jan 26 '15 at 23:22
1

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?..

0
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.

  • I think that drawback is a pretty big problem. As you say, with that query on the SQL Fiddle I get 2 identical rows. I know I could SELECT DISTINCT but I wonder about the efficiency of that. – Migwell Jan 26 '15 at 23:11

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.