pg.ddx.io  pgsql-sql@postgresql.org mailing list archive  
help / color / mirror / Atom feed
GROUP BY / HAVING - am I being thick?
3+ messages / 2 participants
[nested] [flat]

* GROUP BY / HAVING - am I being thick?
@ 2000-04-13 14:38  Andrew C.R. Martin <a.c.r.martin@reading.ac.uk>
  0 siblings, 1 reply; 3+ messages in thread

From: Andrew C.R. Martin @ 2000-04-13 14:38 UTC (permalink / raw)
  To: pgsql-sql


I have a question about GROUP BY / HAVING. It seems I can't do
something I *think* I should be able to do!


Give the following table called 'summary':
------------------------
(int)   (bool) (int)
codon   expl   nexamples
------------------------
 286     t        1
 286     f        1
 286     f        4
 286     t       13
 286     t       37
 286     t        1
 287     f        3
 287     f        3
 287     f        4
 287     f        1
 288     f        2
 288     f        3
 288     f        4
 288     f        2
 289     f        1
 289     t        3
------------------------

For each codon number, I want to obtain sum(nexamples) where expl = 'f';

i.e. I want to get the result:
------------------------
(int)   (int)
codon   sum
------------------------
 286     5
 287     11
 288     11
 289     1
------------------------

I can do this using a temporary table (or equally using a view):
        SELECT codon, nexamples INTO summary_tmp
        FROM summary
        WHERE expl = 'f';
        SELECT codon, sum(nexamples)
        FROM summary_tmp
        GROUP BY codon;
        DROP TABLE summary_tmp;
  
but I thought I should be able to do it using GROUP BY and HAVING.
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';

I've tried various contortions of putting expl in the SELECT clause
and in the GROUP BY clause, but nothing seems to work. The docs say that
PostgreSQL requires the HAVING clause "unambiguously reference a grouping
column" whatever that means - I tried putting expl in the GROUP BY, but it
didn't help.


Any suggestions gratefully received!


Thanks,

Andrew



--
Dr. Andrew C.R. Martin          EMail:  a.c.r.martin@reading.ac.uk  (work)
Lecturer in Bioinformatics              andrew@stagleys.demon.co.uk (home)
University of Reading
Tel.: +44 (0)118 987 5123x7022  Fax:    +44 (0)118 931 0180



^ permalink  raw  reply  [nested|flat] 3+ messages in thread

* Re: GROUP BY / HAVING - am I being thick?
@ 2000-04-13 15:37  Tom Lane <tgl@sss.pgh.pa.us>
  parent: Andrew C.R. Martin <a.c.r.martin@reading.ac.uk>
  0 siblings, 0 replies; 3+ messages in thread

From: Tom Lane @ 2000-04-13 15:37 UTC (permalink / raw)
  To: Andrew C.R. Martin <a.c.r.martin@reading.ac.uk>; +Cc: pgsql-sql

"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



^ permalink  raw  reply  [nested|flat] 3+ messages in thread

* Re: GROUP BY / HAVING - am I being thick?
@ 2000-04-13 15:52  Andrew C.R. Martin <a.c.r.martin@reading.ac.uk>
  0 siblings, 0 replies; 3+ messages in thread

From: Andrew C.R. Martin @ 2000-04-13 15:52 UTC (permalink / raw)
  To: pgsql-sql



> I have a question about GROUP BY / HAVING. It seems I can't do
> something I *think* I should be able to do!
[...]
> SELECT codon, sum(nexamples)
> FROM summary
> WHERE expl = 'f'
> GROUP BY codon
> HAVING expl = 'f';

OK! As I thought, I was being thick - helps to read the book properly instead
of dipping into it. Thanks Tom for clearing this up!


Andrew

--
Dr. Andrew C.R. Martin          EMail:  a.c.r.martin@reading.ac.uk  (work)
Lecturer in Bioinformatics              andrew@stagleys.demon.co.uk (home)
University of Reading
Tel.: +44 (0)118 987 5123x7022  Fax:    +44 (0)118 931 0180



^ permalink  raw  reply  [nested|flat] 3+ messages in thread


end of thread, other threads:[~2000-04-13 15:52 UTC | newest]

Thread overview: 3+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2000-04-13 14:38 GROUP BY / HAVING - am I being thick? Andrew C.R. Martin <a.c.r.martin@reading.ac.uk>
2000-04-13 15:37 ` Tom Lane <tgl@sss.pgh.pa.us>
2000-04-13 15:52 Re: GROUP BY / HAVING - am I being thick? Andrew C.R. Martin <a.c.r.martin@reading.ac.uk>

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