We help IT Professionals succeed at work.

sql CASE statment with COUNT function

nmarun
nmarun asked
on
Medium Priority
1,153 Views
Last Modified: 2012-05-07
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

Comment
Watch Question

CERTIFIED EXPERT
Awarded 2008
Awarded 2008
Commented:
Unlock this solution and get a sample of our free trial.
(No credit card required)
UNLOCK SOLUTION
Commented:
Unlock this solution and get a sample of our free trial.
(No credit card required)
UNLOCK SOLUTION
Top Expert 2009

Author

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.
Kevin CrossChief Technology Officer
CERTIFIED EXPERT
Most Valuable Expert 2011

Commented:
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

CERTIFIED EXPERT
Awarded 2008
Awarded 2008
Commented:
Unlock this solution and get a sample of our free trial.
(No credit card required)
UNLOCK SOLUTION
Kevin CrossChief Technology Officer
CERTIFIED EXPERT
Most Valuable Expert 2011

Commented:
And yes, you will need the END for the case statement.

Commented:
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.
Top Expert 2009

Author

Commented:
Thanks guys
Unlock the solution to this question.
Thanks for using Experts Exchange.

Please provide your email to receive a sample view!

*This site is protected by reCAPTCHA and the Google Privacy Policy and Terms of Service apply.

OR

Please enter a first name

Please enter a last name

8+ characters (letters, numbers, and a symbol)

By clicking, you agree to the Terms of Use and Privacy Policy.