Received: from mail.enterprise.net (root@mail.enterprise.net [194.72.192.18]) by hub.org (8.9.2/8.9.1) with ESMTP id CAA02794 for ; Mon, 22 Feb 1999 02:08:09 -0500 (EST) (envelope-from olly@lfix.co.uk) Received: from linda.lfix.co.uk (root@ppp146.enterprise.net [194.72.195.146]) by mail.enterprise.net (8.8.5/8.8.5) with ESMTP id HAA16909; Mon, 22 Feb 1999 07:08:06 GMT Received: from linda.lfix.co.uk (olly@localhost [127.0.0.1]) by linda.lfix.co.uk (8.9.2/8.9.1/Debian/GNU) with ESMTP id HAA20343; Mon, 22 Feb 1999 07:07:58 GMT Message-Id: <199902220707.HAA20343@linda.lfix.co.uk> X-Mailer: exmh version 2.0.2 2/24/98 (debian) To: gdaswani@odc.net cc: pgsql-sql@postgreSQL.org Subject: Re: [SQL] HAVING confusion.. In-Reply-To: Message from "George Henry C. Daswani" of "Sun, 21 Feb 1999 14:33:09 PST." Mime-Version: 1.0 Content-Type: text/plain; charset=us-ascii Date: Mon, 22 Feb 1999 07:07:58 +0000 From: "Oliver Elphick" "George Henry C. Daswani" wrote: >Hello, > > I'm using postgresql 6.4.2 and i'm having a problem with the >"HAVING" clause.. > > >Basically I do > >SELECT u_id,expire_date FROM users HAVING expire_date >= (NOW()) ... >and it keeps failing with >ERROR: This is not a valid having query! You should only use HAVING with GROUP BY; for what you are doing, you just want WHERE. SELECT u_id,expire_date FROM users WHERE expire_date >= date(now()); u_id |expire_date -----+----------- fred | 21-03-1999 tom | 01-05-1999 harry| 01-05-1999 sarah| 21-03-1999 dick | 12-04-1999 (5 rows) SELECT expire_date,COUNT(*) FROM users GROUP BY expire_date HAVING expire_date >= date(now()); expire_date|count -----------+----- 21-03-1999| 2 12-04-1999| 1 01-05-1999| 2 (3 rows) -- Oliver Elphick Oliver.Elphick@lfix.co.uk Isle of Wight http://www.lfix.co.uk/oliver PGP key from public servers; key ID 32B8FAA1 ======================================== "The LORD bless thee, and keep thee; The LORD make his face shine upon thee, and be gracious unto thee; The LORD lift up his countenance upon thee, and give thee peace." Numbers 6:24-26