Received: from mailhost.rdg.ac.uk (IDENT:exim@sumh1.rdg.ac.uk [134.225.16.4]) by hub.org (8.9.3/8.9.3) with ESMTP id KAA10478 for ; Thu, 13 Apr 2000 10:44:40 -0400 (EDT) (envelope-from a.c.r.martin@reading.ac.uk) Received: from sapc13 ([134.225.167.27] helo=sapc13.rdg.ac.uk) by mailhost.rdg.ac.uk with esmtp (University of Reading Email Service) id {12fkrF-0002wo-00} for pgsql-sql@postgreSQL.org; Thu, 13 Apr 2000 15:44:37 +0100 Received: from localhost (localhost [[UNIX: localhost]]) by sapc13.rdg.ac.uk (8.9.3/8.9.3) id PAA04658 for pgsql-sql@postgreSQL.org; Thu, 13 Apr 2000 15:44:42 +0100 From: "Andrew C.R. Martin" To: pgsql-sql@postgresql.org Subject: GROUP BY / HAVING - am I being thick? Date: Thu, 13 Apr 2000 15:38:27 +0100 X-Mailer: KMail [version 1.0.20] Content-Type: text/plain MIME-Version: 1.0 Message-Id: <0004131544420E.29884@sapc13.rdg.ac.uk> Content-Transfer-Encoding: 8bit X-Archive-Number: 200004/143 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