Column is not found - SQL Server Forums

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

Column is not found

Author

Topic

theseeker
Starting Member

2 Posts

Posted - 2010-08-07 : 03:09:37

SELECT count(iceCreamType) as 'Amount'

FROM iceCreamTable

WHERE Amount > 3

Group by iceCreamType

They say that column is not found. how can that be?

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2010-08-07 : 03:13:20

Run this:

SELECT * FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME = 'iceCreamTable'

You'll see the column list for that table with the above query. Make sure you run it in the right database.

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to my blog

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2010-08-07 : 03:13:46

you can't reference a column alias in your query like that.

You should put the condition in the HAVING clauase


SELECT count(iceCreamType) as 'Amount'
FROM iceCreamTable
WHERE Amount > 3
Group by iceCreamType
having count(iceCreamType) > 3

KH
[spoiler]Time is always against us[/spoiler]

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2010-08-07 : 03:15:20

Or what khtan said.

I missed the fact that an alias was trying to be used there.

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to my blog

theseeker
Starting Member

2 Posts

Posted - 2010-08-07 : 03:29:47

seems like that does not seems to work still. is there any other ways

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2010-08-07 : 06:04:08

What does not work ? Do you get any error ? Post the error message.

Also post some sample data and show the required result

KH
[spoiler]Time is always against us[/spoiler]

kashyap_sql
Posting Yak Master

174 Posts

Posted - 2010-08-07 : 07:27:37

what is the result you expect too

With Regards
Kashyap M

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2010-08-09 : 03:08:57

Have you tried this?

select * from
(
SELECT iceCreamType,count(iceCreamType) as Amount

FROM iceCreamTable

Group by iceCreamType
)
as t
WHERE Amount > 3

Madhivanan

Failing to plan is Planning to fail

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources