pg.ddx.io  pgsql-sql@postgresql.org mailing list archive  
help / color / mirror / Atom feed
From: Andrew C.R. Martin <a.c.r.martin@reading.ac.uk>
To: pgsql-sql@postgresql.org
Subject: GROUP BY / HAVING - am I being thick?
Date: Thu, 13 Apr 2000 15:38:27 +0100
Message-ID: <0004131544420E.29884@sapc13.rdg.ac.uk> (raw)


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



view thread (3+ messages)  latest in thread

Message-ID: <0004131544420E.29884@sapc13.rdg.ac.uk>
Permalink:  ../0004131544420E.29884@sapc13.rdg.ac.uk/
Also on:    postgresql.org/message-id/0004131544420E.29884@sapc13.rdg.ac.uk

 · 

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: a.c.r.martin@reading.ac.uk
  Subject: Re: GROUP BY / HAVING - am I being thick?
  In-Reply-To: <0004131544420E.29884@sapc13.rdg.ac.uk>
  References: <0004131544420E.29884@sapc13.rdg.ac.uk>

* 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