Having can used only with the select statement .it is typically used with group by clause . when it is used without group by it work like where clause
Login to rate this answer.
Ajay Sharma
Answered On : Oct 17th, 2005
WHERE clause is used to impose condition on SELECT statement as well as single row function and is used before GROUP BY clause where as HAVING clause is used to impose condition on GROUP Function and is used after GROUP BY clause in the query
Login to rate this answer.
Ajay Sharma
Answered On : Oct 17th, 2005
WHERE clause is used to impose condition on SELECT statement as well as single row function and is used before GROUP BY clause where as HAVING clause is used to impose condition on GROUP Function and is used after GROUP BY clause in the query
Login to rate this answer.
vini
Answered On : Mar 13th, 2006
1. Having clause is usually used with Group By clause although it can be used without it too.
2. 'Having' is just an additional filter to 'Where' clause.
3. 'Where' clause applies to the individual rows whereas 'Having' clause is used to test some condition on the group(usually aggregate methods) rather than on individual rows.
Login to rate this answer.
culver_lake
Answered On : Mar 19th, 2006
Where and Having are two different things. Having is NOT just another WHERE clause. DB2 will not let you use HAVING without GROUP BY . SYBASE and SQL Server allow HAVING to be used alone but the results can be very unexpected. Be very careful of this, it usually will not produce what you want.
WHERE applies to rows, HAVING applies to summarized rows (summarized with GROUP BY) if you wanted to find the average salary in each department GREATER than333you would code:
SELECT DEPARTMENT, AVG(SALARY)
FROM EMP
WHERE DEPARTMENT > 333
GROUP BY DEPARTMENT
IF you then wanted to filter the intermediate result to contain departments where the average salary was greater that 50,000 you would code:
SELECT DEPARTMENT, AVG(SALARY)
FROM EMP
WHERE DEPARTMENT > 333
GROUP BY DEPARTMENT
HAVING AVG(SALARY) > 50000.
Where executes first
GROUP BY next
and finally HAVING
1 User has rated as useful.
Login to rate this answer.
aruna
Answered On : Apr 3rd, 2006
where clause can be used any type of sql statements
but having clause can be used with only group functions.
Login to rate this answer.
Tushar
Answered On : May 9th, 2007
You can have only group functions in having clause.
Login to rate this answer.
xsgao
Answered On : May 9th, 2007
As stated in the SQL Server help, for Having clause:
Specifies a search condition for a group or an aggregate. HAVING can be used only with the SELECT statement. HAVING is typically used in a GROUP BY clause. When GROUP BY is not used, HAVING behaves like a WHERE clause.
So you can use having WITHOUT group by clause. This is a sample, although it might make no sense of business value:
select max(state) from States having count(State) > 60
Login to rate this answer.
For faster execution of SQL commands always use more conditions in WHERE clause than in HAVING clause.
First SQL server filters the rows using WHERE conditions and then performs group by on remaining rows and then filters the rows agian with HAVING.
Login to rate this answer.
priya
Answered On : Jul 30th, 2007
Here both are filter conditions
where clause is used when from is used in the select condition..
having clause is used when group by function in the select condition....
Login to rate this answer.
hema
Answered On : Aug 2nd, 2007
The basic difference between having and where clause is simply that you cannot use aggregate functions in where clause but in having you can specify
example-having avg(marks)
but try to recall you cannot use something like this-where avg(marks)
Login to rate this answer.
Pls check your answer practically first then only write it.
WE cant use HAVING clause without group functions IT will show error.So pls mr.Rahul Be Carefull next time
Login to rate this answer.
HAVING specifies a search condition for a group or an aggregate function used in SELECT statement.
HAVING can be used only with the SELECT statement. HAVING is typically used in a GROUP BY clause. When GROUP BY is not used, HAVING behaves like a WHERE clause.
Login to rate this answer.
Thirupathi
Answered On : Aug 8th, 2011
Both Are Used Condition Purpose ,But Main Difference is Where Clause Is used Before the GROUP BY , HAVING Clause Is used After the GROUP BY
Login to rate this answer.
Ankush Sharma
Answered On : Sep 13th, 2011
Though the HAVING clause specifies a condition that is similar to the purpose of a WHERE clause, the two clauses are not interchangeable. Listed below are some differences to help distinguish between the two:
1. The WHERE clause specifies the criteria which individual records must meet to be selcted by a query. It can be used without the GROUP BY clause. The HAVING clause cannot be used without the GROUP BY clause.
2. The WHERE clause selects rows before grouping. The HAVING clause selects rows after grouping.
3. The WHERE clause cannot contain aggregate functions. The HAVING clause can contain aggregate functions.
for Example: if for an "Select" statement we use the "where" clause then the the result based on the "where" condition results and then we can use "group by" clause to arrange in some order, Now if we want to impose the condition on that group then we use "having" clause.
Login to rate this answer.
Ankush Sharma
Answered On : Sep 13th, 2011
Though the HAVING clause specifies a condition that is similar to the purpose of a WHERE clause, the two clauses are not interchangeable. Listed below are some differences to help distinguish between the two:
1. The WHERE clause specifies the criteria which individual records must meet to be selcted by a query. It can be used without the GROUP BY clause. The HAVING clause cannot be used without the GROUP BY clause.
2. The WHERE clause selects rows before grouping. The HAVING clause selects rows after grouping.
3. The WHERE clause cannot contain aggregate functions. The HAVING clause can contain aggregate functions.
for Example: if for an "Select" statement we use the "where" clause then the the result based on the "where" condition results and then we can use "group by" clause to arrange in some order, Now if we want to impose the condition on that group then we use "having" clause.
The main reason for using WHERE clause is to select rows that are to be included in the query. For example, assume table Test.Suppose I want the names, account numbers, and balance due of all customers from California and Los Angles. Since STATE is one of the fields in the record format, I can use WHERE to select those customers.
Using the code
Code
SELECT cusnum, lstnam, init
FROM Test
WHERE state IN ('CA', 'LA')
CUSNUM LSTNAM INIT BALDUE
====== ============ ==== ========
938472 John G K 37.00
938485 Mark J A 3987.50
593029 Lily E D 25.00
Suppose I want the total amount due from customers by state. In that case, I would need to use the GROUP BY clause to build an aggregate query.
Code
SELECT state,SUM(baldue)
FROM Test
GROUP BY state
ORDER BY state
State Sum(Baldue)
===== ===========
CA 250.00
CO 58.75
GA 3987.50
MN 510.00
NY 589.50
TX 62.00
VT 439.00
WY .00
Using Having
Code
SELECT state,SUM(baldue)
FROM Test
GROUP BY state
HAVING SUM(baldue) > 250
State Sum(Baldue)
===== ===========
GA 3987.50
MN 510.00
NY 589.50
VT 439.00
1 User has rated as useful.
Login to rate this answer.
