Tek-Tips is the largest IT community on the Internet today!
Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!
-
Congratulations strongm on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!
having-where
- Thread starter keizersoz
- Start date
Straight out of Books Online, but i believe it answers the first part of your question !!
HAVING
Specifies a search condition for a group or an aggregate. HAVING can be used only with the SELECT statement. It is usually used in a GROUP BY clause. When GROUP BY is not used, HAVING behaves like a WHERE clause.
By aggregate Function, if you mean in line function, which return a single Value, then Yes i believe that you can !
If you are taking about SQL in built function Like COUNT or AVG, then YES once again you can do this !
HAVING
Specifies a search condition for a group or an aggregate. HAVING can be used only with the SELECT statement. It is usually used in a GROUP BY clause. When GROUP BY is not used, HAVING behaves like a WHERE clause.
By aggregate Function, if you mean in line function, which return a single Value, then Yes i believe that you can !
If you are taking about SQL in built function Like COUNT or AVG, then YES once again you can do this !
Aggregate functions are intended for use in the HAVING clause, not the WHERE. Other than the aggregate functions, the key difference between the two is when they are applied.
The WHERE conditions are applied for each row of original data, as they are read, before the selected fields are extracted.
The HAVING conditions are applied after the selected fields are extracted, after any WHERE conditions and after any GROUP BY groupings are applied.
The WHERE conditions are applied for each row of original data, as they are read, before the selected fields are extracted.
The HAVING conditions are applied after the selected fields are extracted, after any WHERE conditions and after any GROUP BY groupings are applied.
tektipdjango
IS-IT--Management
- Jan 28, 2004
- 188
avoid using selections by Having as they are made after the all recordset is retrieved, a lot time and ressource consuming !!
So limit the having clause to aggregate selections when it's not possible another way;
All other selections must be made in the where clause (or the on clause sometimes)
So limit the having clause to aggregate selections when it's not possible another way;
All other selections must be made in the where clause (or the on clause sometimes)
--------------------------------------------------
[highlight]Django[/highlight]![[thumbsup]](/data/assets/smilies/thumbsup.gif "[thumbsup] [thumbsup]")
bug exterminator
tips'n tricks addict
[highlight]Django[/highlight]
bug exterminator
tips'n tricks addict
NullTerminator
Programmer
The where clause is evaluated before the aggregation of data, and the having is evaluated after the aggregation or grouping takes place
ex
select store, avg(sales) from t_Sales
where month = 'may'
group by store
having avg(sales) > 10000
You can't evaluate the avg(sales) in the where clause, because at the table level, there is no average, so it is done using the having clause.
The where clause limits the records that are averaged to those for the month of may. This happpens prior to the grouping.
\0
ex
select store, avg(sales) from t_Sales
where month = 'may'
group by store
having avg(sales) > 10000
You can't evaluate the avg(sales) in the where clause, because at the table level, there is no average, so it is done using the having clause.
The where clause limits the records that are averaged to those for the month of may. This happpens prior to the grouping.
\0
- Status
Similar threads
- Locked
- Question
- Locked
- Question
