a stupid question but a hard one

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 2000 Forums

SQL Server Development (2000)

a stupid question but a hard one

Author

Topic

Ex
Posting Yak Master

166 Posts

Posted - 2005-05-05 : 21:09:45

hey all i have a really odd quesiton for you all

just wondering is there anyway to do a count on a column with a condition so say where col = 1 if you cant change the where clause in the statment? where clause is produced by another program

i was tyrin to use case statment was kinda right but just want the count part got answer

null
null
2
null

select case when col1= 1 then count(*) else end from build
group by col1

any idea how i can do this? i dont think its possible just got asked by another programmer if i knew how?

------------------------------------------------------------------

Programming today is a race between software engineers striving to build bigger and better idiot-proof programs, and the Universe trying to produce bigger and better idiots. So far, the Universe is winning.
Rich Cook

derrickleggett
Pointy Haired Yak DBA

4184 Posts

Posted - 2005-05-05 : 21:17:07

You almost had it right.


DECLARE @count TABLE(col1 INT)

INSERT @count(col1)
	SELECT NULL UNION ALL
	SELECT 1 UNION ALL
	SELECT NULL UNION ALL
	SELECT 1 UNION ALL
	SELECT NULL

SELECT SUM(CASE WHEN ISNULL(col1,0) = 1 THEN 1 ELSE 0 END)
FROM @count

MeanOldDBA
derrickleggett@hotmail.com

When life gives you a lemon, fire the DBA.

Ex
Posting Yak Master

166 Posts

Posted - 2005-05-05 : 22:00:41

hehe that worked nice

but heres a tester for ya possible to do it without storing all values in a temp table and sifting through the nulls to get the answer?

can i do it in one setp? i doubt it but i will pose the question anyway :)

------------------------------------------------------------------

Programming today is a race between software engineers striving to build bigger and better idiot-proof programs, and the Universe trying to produce bigger and better idiots. So far, the Universe is winning.
Rich Cook

derrickleggett
Pointy Haired Yak DBA

4184 Posts

Posted - 2005-05-05 : 22:16:35

Programming today is a race between software engineers striving to build bigger and better idiot-proof programs, and the Universe trying to produce bigger and better idiots. So far, the Universe is winning.

MeanOldDBA
derrickleggett@hotmail.com

When life gives you a lemon, fire the DBA.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources