6

Suppose I have two tables:

Foo:

id
baz

Bar:

id
foo_id
boom

So a Foo has many Bars. I frequently find myself in situations where I need to compute an aggregate across the Bars for a given set of Foos but I also want some properties from Foo. The two most straightforward ways of doing this are ugly:

Method #1: Unnecessary Aggregate Functions

select
  foo.id,
  min(foo.baz) as baz,
  min(bar.boom) as min_boom
from
  foo
join
  bar on foo.id = bar.foo_id
group by
  foo.id;

Method #2: Unnecessary Group By Column

select
  foo.id,
  foo.baz,
  min(bar.boom) as min_boom
from
  foo
join
  bar on foo.id = bar.foo_id
group by
  foo.id,
  foo.baz;

This is not so bad when there's only one additional column from Foo besides "id", but if there are many columns that need to be included then the grouping becomes much less efficient. A query like this gets around both these issues, but seems unwieldy:

select
  foo.id,
  foo.baz,
  x.min_boom
from
  foo
join
  (select
    foo_id, 
    min(boom) as min_boom
  from
    bar
  group by
    foo_id) x on x.foo_id = foo.id;

Is there a better way? Platform is Postgres if that matters.

10

If id is defined as the primary key, you can omit grouping by all the foo columns you want for the output as long as you are grouping by the id. This special case of grouping is in accordance with the current SQL standard and has also been covered in the PostgreSQL manual, starting from version 9.1:

When GROUP BY is present, or any aggregate functions are present, it is not valid for the SELECT list expressions to refer to ungrouped columns except within aggregate functions or when the ungrouped column is functionally dependent on the grouped columns, since there would otherwise be more than one possible value to return for an ungrouped column. A functional dependency exists if the grouped columns (or a subset thereof) are the primary key of the table containing the ungrouped column.

(Emphasis added.)

So, if foo.id is the PK, this query would be valid:

select
  foo.id,
  foo.baz,
  foo.whatever,
  min(bar.boom) as min_boom
from
  foo
join
  bar on foo.id = bar.foo_id
group by
  foo.id;
  • 4
    Yes! The restriction is that it has to be the PRIMARY KEY, it hasn't been (yet) implemented for UNIQUE constraints. – ypercubeᵀᴹ May 17 '16 at 7:29
3

PostgreSQL's DISTINCT ON is very elegant and performs very well (often better than aggregates):

select DISTINCT ON (foo.id, foo.baz)
  foo.id,
  foo.baz,
  bar.boom as min_boom
from
  foo
join
  bar on foo.id = bar.foo_id
ORDER BY
  foo.id,
  foo.baz,
  bar.boom;

Or

select
  foo.id,
  foo.baz,
  x.min_boom
from
  foo
join
  (select DISTINCT ON (foo_id)
    foo_id, 
    boom as min_boom
  from
    bar
  ORDER BY
    foo_id,
    boom) x on x.foo_id = foo.id;

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.