select query - 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

SQL Server 2005 Forums

Transact-SQL (2005)

select query

Author

Topic

mkool
Starting Member

25 Posts

Posted - 2008-02-22 : 13:42:13

select list_ , case when additional_ is null then 'not identified'
else additional_
end as additional_ , @a, state_ ,@b

from members_ m

where list_ = 'listdesc' and membertype_ = 'normal'

i want output like:

list_ additional_ no.of additional_ state no.ofstates_
----------------------------------------------------------------------listdesc advocate 3 nj 3
listdesc not identified 5 ny 9

if i m doing
set @a = select count(additional_) from members_
it gives me error as additional_ - datatype is text

Msg 279, Level 16, State 2, Line 1
The text, ntext, and image data types are invalid in this subquery or aggregate expression.

can anyone help me to get correct results.
thanks

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-02-22 : 15:09:39

count(*) where x is not null

E 12°55'05.25"
N 56°04'39.16"

jackv
Master Smack Fu Yak Hacker

2179 Posts

Posted - 2008-02-23 : 12:38:41

declare @a INT ,@b INT

select list_ , case when additional_ is null then 'not identified'
else additional_
end as additional_ ,
counts=
case when additional_ is not null THEN count(*)
else
-1
END
, state_ ,@b

from members_ m

where list_ = 'listdesc' and membertype_ = 'normal'

Jack Vamvas
--------------------
Search IT jobs from multiple sources- http://www.ITjobfeed.com

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources