-
What's wrong with this statement :
SELECT department "Departments", MAX(salary) "Tope Salaries"
FROM employee
WHERE department IN(200, 300, 400)
GROUP BY Departments
HAVING MAX(salary) > 60000;
Thanks
-
You can't use GROUPBY and HAVING in the SQL statement.
-
I can not figour out where is the mistake in your statement .
I think it's correct . Groupby and Having are the basic in SQL .
-
SELECT department "Departments", MAX(salary) "Tope Salaries"
FROM employee
WHERE department IN(200, 300, 400)
GROUP BY Department[s!!!!]
HAVING MAX(salary) > 60000;
-
Is that Right ? Can we use GROUPBY and HAVING in the same statement ? There is some restriction, the order of usage of clauses or something,i don't remember anymore.
someone clarify me. thanks
[Edited by sreddy on 01-05-2001 at 08:59 AM]
-
[QUOTE][i]Originally posted by sreddy [/i]
[B]Is that Right ? Can we use GROUPBY and HAVING in the same statement ? There is some restriction, the order of usage of clauses or something,i don't remember anymore.
someone clarify me. thanks
[Edited by sreddy on 01-05-2001 at 08:59 AM] [/B][/QUOTE]
Sreddy,
The combination of GROUP BY and HAVING in the same selct is actually the most "natural" combination - you can say they were invented to work hand in hand from the very begining of SQL standard.
As soon as you are using HAVING you must have a group expression and as soon as you have group function you (in most cases) have to use GROUP BY.....
HTH,
Jurij Modic
ASCII a stupid question, get a stupid ANSI
24 hours in a day .... 24 beer in a case .... coincidence?
-
Ok the problem was I used column alias ( Departments) instead of real column name in group by clause
Thanks
-
You can use GROUP BY and HAVING in the same query, but not GROUP BY and WHERE.
Try with a view.
Valerie
-
thx guys!
I was confused. Its with GROUP BY and ORDER BY clauses. This is what, I meant and got confused with GROUP BY and HAVING... Silly!
------------------------------------------------------------------------------------------------
If you specify a group_by_clause in the same statement, this order_by_clause is restricted to the following expressions:
Constants
Aggregate functions
Analytic functions
The functions USER, UID, and SYSDATE
Expressions identical to those in the group_by_clause
Expressions involving the above expressions that evaluate to the same value for all rows in a group.
-
-------------------
vgeraux :
You can use GROUP BY and HAVING in the same query, but not GROUP BY and WHERE.
Try with a view.
---------------------
Also incorrect. You can use a WHERE with a GROUP BY and a HAVING. This is not a problem. The WHERE restricts the original records that are subsequently grouped using the GROUP BY, creating a new recordset. This recordset is then restricted by the HAVING clause.
- Chris
Posting Permissions
- You may not post new threads
- You may not post replies
- You may not post attachments
- You may not edit your posts
-
Forum Rules
|
Click Here to Expand Forum to Full Width
|