Having accesses "derived" data - meaning a result set is first built then winnnowed out. In that case aggregate
functions are not used in the where clause because the where clause filters in advance.
More to the point, the example you give is not useful without a group by clause. It simply says:
compare count all values where your count of all values is greater than 0. This would not be possible except after the
fact (in fact it will make your head hurt if you think about it).
Most aggregate functions are NOT useful without a "group by clause" - unless your purpose is to return results based on
an entire table. An aggregate function will return a single value or row (always) without a group by - as in this
example:
SELECT count(id) AS mycount, points
FROM users
WHERE points IS NOT NULL
AND points <> 0.00
GROUP BY points
HAVING count(id) > 0
ORDER BY points
Note: Use "having" wisely and tune your temp db and other params on the sql server accordingly. Remember you are
creating a large recordset and then culling out values you don't want - all before the data is returned.
-Mark
-----Original Message-----
From: [EMAIL PROTECTED] [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED]
Sent: Wednesday, October 01, 2003 4:09 PM
To: CF-Talk
Subject: Re:sql question pt 2
I agree with Casey - something is wrong with this query and thread (any SQL Ninjas out there).
Let's simplify: On MS SQL Server v7, the following trivial query DOES work -
SELECT count(author_id) as myCounter
FROM author
HAVING count(author_id) > 0
The following (same DB etc), DOES NOT WORK:
SELECT count(author_id) as myCounter
FROM author
WHERE count(author_id) > 0
>I have never been able to reference a name I have assigned to a column in
>the where clause. Also, In an SQL book I have it says "The use of a
>HAVING clause without a corresponding GROUP BY clause is almost never seen
>in practice". (LAN TIMES Guide to SQL)
>
>I would agree with J E VanOver with the following:
>
>SELECT count(tony) as tonysCount, sum(jayme) as jaymesCount from orders
>WHERE Abs((count(tony)-sum(jayme))/count(tony)) > 0
>
>Thanks,
>CC
>
[Todays Threads] [This Message] [Subscription] [Fast Unsubscribe] [User Settings]
