Solved

better way to get a count

Posted on 2008-10-13
13
229 Views
Last Modified: 2010-04-21
hello
currently am getting a count based on a catid as in:
select a,b,c,d,
(select count(*)
from tableb b
where b.catid = a.catid) as count
from tablea a
where whatever

ok so then I have a situation where I need to say
if (select count(*)
from tableb b
where b.catid = a.catid)
> 0 then
(select count(*)
from tableb b
where b.catid = a.catid)
else
(select count(*)
from tablec c
where c.catid = a.catid)
end
as count

Q: this seems inefficient
what should I do in situations like this?

thanks
0
Comment
Question by:dgrafx
  • 4
  • 4
  • 4
  • +1
13 Comments
 
LVL 17

Expert Comment

by:HuyBD
ID: 22708254
try this
case when (select count(*)

from tableb b

where b.catid = a.catid)

> 0 then

(select count(*)

from tableb b

where b.catid = a.catid)

else

(select count(*)

from tablec c

where c.catid = a.catid)

end

as count

Open in new window

0
 
LVL 17

Expert Comment

by:HuyBD
ID: 22708266
completed query
select a,b,c,d,

case when (select count(*)

from tableb b

where b.catid = a.catid)

> 0 then

(select count(*)

from tableb b

where b.catid = a.catid)

else

(select count(*)

from tablec c

where c.catid = a.catid)

end

as count

from tablea a

Open in new window

0
 
LVL 25

Author Comment

by:dgrafx
ID: 22708276
ok - i typed wrong
what i'm using is
case when ...

so what i was concerned about was the overhead from counting at least twice
I keep thinking there might be a way to count once

any ideas on that?
0
 
LVL 59

Expert Comment

by:Kevin Cross
ID: 22708286
Try this:
select a.a, a.b, a.c, a.d,

case isnull(b.countB, 0) when 0 then isnull(c.countC,0) 

else b.countB end as [count]

from tablea a 

LEFT JOIN (select catid, count(*) as countB from tableb group by catid) b 

ON a.catid = b.catid

LEFT JOIN (select catid, count(*) as countC from tablec group by catid) c

ON a.catid = c.catid

where somecolumn = 'whatever'

Open in new window

0
 
LVL 59

Expert Comment

by:Kevin Cross
ID: 22708295
An alternative would be to make your original query a derived query and only do the second count like this:
SELECT a,b,c,d, 

case count when 0 then 

(select count(*)

from tablec c

where c.catid = derived.catid)

else count end AS count

FROM (select a,b,c,d,

(select count(*)

from tableb b

where b.catid = a.catid) as count

from tablea a

where somecolumn='whatever') derived

Open in new window

0
 
LVL 17

Expert Comment

by:HuyBD
ID: 22708322
0
What is SQL Server and how does it work?

The purpose of this paper is to provide you background on SQL Server. It’s your self-study guide for learning fundamentals. It includes both the history of SQL and its technical basics. Concepts and definitions will form the solid foundation of your future DBA expertise.

 
LVL 17

Accepted Solution

by:
HuyBD earned 500 total points
ID: 22708337
try this
select a,b,c,d,

case count1 when 0 then count2 else count1 end as count

from (select a,b,c,d,(select count(*)

from tableb b

where b.catid = a.catid) as count1

(select count(*)

from tablec c

where c.catid = a.catid) as count2

from tablea a) as T

Open in new window

0
 
LVL 5

Expert Comment

by:harwantgrewal
ID: 22708415
My Suggestion that as you are checking the count from a table and then if its greater then 0 then you want to output that else count from table c.

Harry
Declare @countTableA int
 

select @countTableA = count(*) from tablec b where b.catid = a.catid
 

if @countTableA > 0

BEGIN

   Select @countTableA

END

ELSE

BEGIN

  select count(*) from tablec c where c.catid = a.catid

END

Open in new window

0
 
LVL 59

Expert Comment

by:Kevin Cross
ID: 22708431
dgrafx, I would use a join to both sets of data as shown here: http:#22708286; however, you could also use derived table like the suggestion following the one linked here OR a common table expression allowing you to do the count once and then do additional logic based on that result in an outer query.

Hopefully that helps.

Regards,
Kevin
0
 
LVL 25

Author Comment

by:dgrafx
ID: 22708453
looks good - will test some more tomorrow - it's late here

harwantgrewal: could you explain how you would 'blend' that into an example like:
select a,b,c,d,(select count(*) from tableb) as count
from tablea
0
 
LVL 25

Author Closing Comment

by:dgrafx
ID: 31505766
this one seemed to work out the best and thanks to everyone
0
 
LVL 59

Expert Comment

by:Kevin Cross
ID: 22710424
LOL.

Glad we were able to help you find an alternative solution.

Regards,
Kevin
0
 
LVL 25

Author Comment

by:dgrafx
ID: 22710435
thanks Kevin
0

Featured Post

[Webinar] Disaster Recovery and Cloud Management

Learn from Unigma and CloudBerry industry veterans which providers are best for certain use cases and how to lower cloud costs, how to grow your Managed Services practice in IaaS clouds, and how to utilize public cloud for Disaster Recovery

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
Modifying SQL 2008/2012 PARTITIONS 3 55
Get row count of current SQL query 8 45
Problem with SqlConnection 4 160
how to eliminate duplicates in a string variable in t-sql? 30 63
In SQL Server, when rows are selected from a table, does it retrieve data in the order in which it is inserted?  Many believe this is the case. Let us try to examine for ourselves with an example. To get started, use the following script, wh…
In this article we will get to know that how can we recover deleted data if it happens accidently. We really can recover deleted rows if we know the time when data is deleted by using the transaction log.
Along with being a a promotional video for my three-day Annielytics Dashboard Seminor, this Micro Tutorial is an intro to Google Analytics API data.
Migrating to Microsoft Office 365 is becoming increasingly popular for organizations both large and small. If you have made the leap to Microsoft’s cloud platform, you know that you will need to create a corporate email signature for your Office 365…

920 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

16 Experts available now in Live!

Get 1:1 Help Now