pg.ddx.io  pgsql-sql@postgresql.org mailing list archive  
help / color / mirror / Atom feed
From: Tom Lane <tgl@sss.pgh.pa.us>
To: Andrew C.R. Martin <a.c.r.martin@reading.ac.uk>
Cc: pgsql-sql@postgresql.org
Subject: Re: GROUP BY / HAVING - am I being thick?
Date: Thu, 13 Apr 2000 11:37:45 -0400
Message-ID: <10966.955640265@sss.pgh.pa.us> (raw)
In-Reply-To: <0004131544420E.29884@sapc13.rdg.ac.uk>
References: <0004131544420E.29884@sapc13.rdg.ac.uk>

"Andrew C.R. Martin" <a.c.r.martin@reading.ac.uk> writes:
> For each codon number, I want to obtain sum(nexamples) where expl = 'f';

> The following looks as though it should work according to "The
> Practical SQL Handbook" (Bowman, Emerson & Darnovsky)

> SELECT codon, sum(nexamples)
> FROM summary
> WHERE expl = 'f'
> GROUP BY codon
> HAVING expl = 'f';

Just drop the HAVING clause and that will work --- you're already
filtering by expl at the WHERE stage, so there's no need to do it
again after grouping (quite aside from the fact that you can't
reference an ungrouped column in HAVING).

The thing to remember about WHERE vs HAVING is that WHERE filters tuples
before they are aggregated into groups, while HAVING filters afterwards.
Whatever tuples get past the WHERE filter are collected into groups,
and then HAVING is applied to the per-group output tuples to filter out
whole groups that you don't want.

In practice, you only need HAVING if you want to filter on aggregate
functions of the groups.  For instance, if in your example you didn't
want to hear about codons with sum(nexamples) < 100, you'd do

SELECT codon, sum(nexamples)
FROM summary
WHERE expl = 'f'
GROUP BY codon
HAVING sum(nexamples) >= 100;

Once you realize that HAVING applies post-group, it should be pretty
clear why the HAVING clause can only reference group-column values
and aggregate results.  Anything else is not well-defined at the
group level.  For instance, the group for codon=286 has several
different values for nexamples, so you can't ask about a specific
value of nexamples in the HAVING clause --- which one would you get?

			regards, tom lane



view thread (3+ messages)  latest in thread

Message-ID: <10966.955640265@sss.pgh.pa.us>
Permalink:  ../10966.955640265@sss.pgh.pa.us/
Also on:    postgresql.org/message-id/10966.955640265@sss.pgh.pa.us

 · 

reply

Reply instructions:

You may reply publicly to this message via plain-text email
using any one of the following methods:

* Reply to all the recipients from your mail client; the link fills in
  the headers below: reply via email
  Some clients, webmail especially, drop In-Reply-To and References from
  a link, and the reply then starts a new thread: use the mbox below.

  To: pgsql-sql@postgresql.org
  Cc: tgl@sss.pgh.pa.us, a.c.r.martin@reading.ac.uk
  Subject: Re: GROUP BY / HAVING - am I being thick?
  In-Reply-To: <10966.955640265@sss.pgh.pa.us>
  References: <0004131544420E.29884@sapc13.rdg.ac.uk> <10966.955640265@sss.pgh.pa.us>

* Save the following mbox file, import it into your mail client,
  and reply-to-all from there: mbox

This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox