sql CASE statment with COUNT function

Hi,

I want to know how to write the query to run efficiently.
In english:
if the count is
0, return just the count (zero)
else, return the formatted string

I believe I'm calling the count multiple times and I want to see if there's a better way to do this.

Thanks
Arun
SELECT 
      CASE Count(ItemID)
		WHEN 0 THEN '0'
		ELSE '<a href="link here/" + Count(ItemID) + ">here</a>"'
FROM ItemMaster

Open in new window

LVL 27
nmarunAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

chapmandewCommented:
Its fine, but here is a different way to do it

SELECT
CASE WHEN counter = 0 then '0' else '<a href="link here/" + cast(counter as vachar(5)) + ">here</a>"'
FROM
(
SELECT
COUNTER = Count(ItemID)
FROM ItemMaster
) a
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
pssandhuCommented:
SELECT CASE WHEN Count(ItemID) = 0 THEN '0'  
                             ELSE '<a href="link here/" + Count(ItemID) + ">here</a>"'
                 END  as ColName
FROM ItemMaster
0
nmarunAuthor Commented:
chapmandew, are you saying that doing the count(itemId) multiple times will not have a big impact on the query's performance?

pssandhu, thanks for completing my query (i forgot the END part), but you are using the Count(ItemID) multiple times. This is what I want to avoid.
0
Determine the Perfect Price for Your IT Services

Do you wonder if your IT business is truly profitable or if you should raise your prices? Learn how to calculate your overhead burden with our free interactive tool and use it to determine the right price for your IT services. Download your free eBook now!

Kevin CrossChief Technology OfficerCommented:
The above are correct, just remember to add the close/open ' between the literal link text and count.
SELECT
CASE WHEN counter = 0 then '0' else '<a href="link here/"' + cast(counter as vachar(5)) + '">here</a>"'
FROM
(
SELECT 
COUNTER = Count(ItemID)
FROM ItemMaster
) a

Open in new window

0
chapmandewCommented:
No, its not going to affect it much.
0
Kevin CrossChief Technology OfficerCommented:
And yes, you will need the END for the case statement.
0
pssandhuCommented:
Yea, I do not think it going to impact the query performance a lot. However, if you are delaing with lots of tables and records and you are waiting for the query to finish in more than a minute then post your whole query and we look into optimising it.
P.
0
nmarunAuthor Commented:
Thanks guys
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server

From novice to tech pro — start learning today.