>My 2cents,
>
>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]
>com]
>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]
