In SQL, HAVING clause cannot be used without GROUP

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

In SQL, HAVING clause cannot be used without GROUP

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 function


select aggregate_function ( column )
from   some_table
having 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 MVP
http://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 below


declare @t table
(
id int,
Val varchar(100)
)

insert @t
values (1,'Val1'),
(2,'Val2'),
(3,'Val1')

select *
from @t
having Val='val1'


Msg 8121, Level 16, State 1, Line 14
Column '@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 MVP
http://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 can


declare @t table
(
id int,
Val varchar(100)
)

insert @t
values (1,'Val1'),
(2,'Val2'),
(3,'Val1')

select count(case when Val = 'Val1' then Val end)
from @t
having count(case when Val = 'Val1' then Val end) >0

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://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 FALSE
In 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 FALSE
In SQL, HAVING clause cannot be used without GROUP BY operation.

what do you think based on what I illustrated so far?

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://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

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources