Solved

sql CASE statment with COUNT function

Posted on 2009-06-29
8
1,132 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
 
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
PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

 
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

VMware Disaster Recovery and Data Protection

In this expert guide, you’ll learn about the components of a Modern Data Center. You will use cases for the value-added capabilities of Veeam®, including combining backup and replication for VMware disaster recovery and using replication for data center migration.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
Optimizing a query 3 34
Need to update TableA to TableB 6 34
Help with Sorting Full Text results 2 14
MSDN Licensing query 5 53
Nowadays, some of developer are too much worried about data. Who is using data, who is updating it etc. etc. Because, data is more costlier in term of money and information. So security of data is focusing concern in days. Lets' understand the Au…
JSON is being used more and more, besides XML, and you surely wanted to parse the data out into SQL instead of doing it in some Javascript. The below function in SQL Server can do the job for you, returning a quick table with the parsed data.
Via a live example, show how to extract insert data into a SQL Server database table using the Import/Export option and Bulk Insert.
Viewers will learn how to use the SELECT statement in SQL and will be exposed to the many uses the SELECT statement has.

896 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

Need Help in Real-Time?

Connect with top rated Experts

14 Experts available now in Live!

Get 1:1 Help Now