Solved

sql CASE statment with COUNT function

Posted on 2009-06-29
8
1,134 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

0
Comment
Question by:nmarun
  • 2
  • 2
  • 2
  • +1
8 Comments
 
LVL 60

Accepted Solution

by:
chapmandew earned 150 total points
ID: 24737890
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
 
LVL 17

Assisted Solution

by:pssandhu
pssandhu earned 50 total points
ID: 24737902
SELECT CASE WHEN Count(ItemID) = 0 THEN '0'  
                             ELSE '<a href="link here/" + Count(ItemID) + ">here</a>"'
                 END  as ColName
FROM ItemMaster
0
 
LVL 27

Author Comment

by:nmarun
ID: 24737925
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
Optimizing Cloud Backup for Low Bandwidth

With cloud storage prices going down a growing number of SMBs start to use it for backup storage. Unfortunately, business data volume rarely fits the average Internet speed. This article provides an overview of main Internet speed challenges and reveals backup best practices.

 
LVL 59

Expert Comment

by:Kevin Cross
ID: 24737944
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
 
LVL 60

Assisted Solution

by:chapmandew
chapmandew earned 150 total points
ID: 24737958
No, its not going to affect it much.
0
 
LVL 59

Expert Comment

by:Kevin Cross
ID: 24737959
And yes, you will need the END for the case statement.
0
 
LVL 17

Expert Comment

by:pssandhu
ID: 24737965
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
 
LVL 27

Author Closing Comment

by:nmarun
ID: 31598010
Thanks guys
0

Featured Post

Microsoft Certification Exam 74-409

Veeam® is happy to provide the Microsoft community with a study guide prepared by MVP and MCT, Orin Thomas. This guide will take you through each of the exam objectives, helping you to prepare for and pass the examination.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Title # Comments Views Activity
sql server computed columns 11 31
Querying data from 3 SQL tables 2 31
SSRS Enable Remote Errors 4 25
Query to capture 5 and 9 digit zip code? 4 20
Load balancing is the method of dividing the total amount of work performed by one computer between two or more computers. Its aim is to get more work done in the same amount of time, ensuring that all the users get served faster.
For both online and offline retail, the cross-channel business is the most recent pattern in the B2C trade space.
Familiarize people with the process of retrieving data from SQL Server using an Access pass-thru query. Microsoft Access is a very powerful client/server development tool. One of the ways that you can retrieve data from a SQL Server is by using a pa…
Viewers will learn how to use the INSERT statement to insert data into their tables. It will also introduce the NULL statement, to show them what happens when no value is giving for any given column.

776 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question