Re: [SQL] HAVING confusion..

"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(dot)Elphick(at)lfix(dot)co(dot)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