Author |
Topic |
geetha_win
Starting Member
5 Posts |
Posted - 2013-10-17 : 03:02:27
|
In SQL, HAVING clause cannot be used without GROUP BY operation. |
|
khtan
In (Som, Ni, Yak)
17689 Posts |
Posted - 2013-10-17 : 03:13:08
|
actually you can, SQL Server will not complain if you don't have the GROUP BY clause, provided that you do not specify any column name without any aggregate functionselect aggregate_function ( column )from some_tablehaving aggregate_function ( column ) = some_value but i have not thought of any use of that, if anybody have, please enlighten me KH[spoiler]Time is always against us[/spoiler] |
 |
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2013-10-17 : 04:08:55
|
It can be used but most often we associate HAVING with GROUP BY. The filters before aggregation are added to WHERE clause and filtering after aggregation are done using HAVING while applying GROUP BY------------------------------------------------------------------------------------------------------SQL Server MVPhttp://visakhm.blogspot.com/https://www.facebook.com/VmBlogs |
 |
|
geetha_win
Starting Member
5 Posts |
Posted - 2013-10-17 : 04:31:43
|
Thank you for all your quick response, I concluded HAVING clause should be either contained in aggregate function or the GROUP BY clause,I want above statement is true or false |
 |
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2013-10-17 : 04:45:29
|
quote: Originally posted by geetha_win Thank you for all your quick response, I concluded HAVING clause should be either contained in aggregate function or the GROUP BY clause,I want above statement is true or false
yes thats true You can test it using an example as belowdeclare @t table(id int,Val varchar(100))insert @tvalues (1,'Val1'),(2,'Val2'),(3,'Val1')select *from @thaving Val='val1'Msg 8121, Level 16, State 1, Line 14Column '@t.Val' is invalid in the HAVING clause because it is not contained in either an aggregate function or the GROUP BY clause. ------------------------------------------------------------------------------------------------------SQL Server MVPhttp://visakhm.blogspot.com/https://www.facebook.com/VmBlogs |
 |
|
geetha_win
Starting Member
5 Posts |
Posted - 2013-10-17 : 05:18:00
|
My doubt is it can be used with aggregate functions without GROUP BY |
 |
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2013-10-17 : 05:20:58
|
It candeclare @t table(id int,Val varchar(100))insert @tvalues (1,'Val1'),(2,'Val2'),(3,'Val1')select count(case when Val = 'Val1' then Val end)from @thaving count(case when Val = 'Val1' then Val end) >0 ------------------------------------------------------------------------------------------------------SQL Server MVPhttp://visakhm.blogspot.com/https://www.facebook.com/VmBlogs |
 |
|
geetha_win
Starting Member
5 Posts |
Posted - 2013-10-17 : 05:22:50
|
So the below statement is FALSEIn SQL, HAVING clause cannot be used without GROUP BY operation. |
 |
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2013-10-17 : 07:39:59
|
quote: Originally posted by geetha_win So the below statement is FALSEIn SQL, HAVING clause cannot be used without GROUP BY operation.
what do you think based on what I illustrated so far? ------------------------------------------------------------------------------------------------------SQL Server MVPhttp://visakhm.blogspot.com/https://www.facebook.com/VmBlogs |
 |
|
geetha_win
Starting Member
5 Posts |
Posted - 2013-10-18 : 01:48:40
|
It is True that In SQL, HAVING clause cannot be used without GROUP |
 |
|
|