Want to win a PS4? Go Premium and enter to win our High-Tech Treats giveaway. Enter to Win

x
?
Solved

better way to get a count

Posted on 2008-10-13
13
Medium Priority
?
237 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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
Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
LVL 60

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 60

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
 
LVL 17

Accepted Solution

by:
HuyBD earned 2000 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 60

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 60

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

 [eBook] Windows Nano Server

Download this FREE eBook and learn all you need to get started with Windows Nano Server, including deployment options, remote management
and troubleshooting tips and tricks

Question has a verified solution.

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

If you having speed problem in loading SQL Server Management Studio, try to uncheck these options in your internet browser (IE -> Internet Options / Advanced / Security):    . Check for publisher's certificate revocation    . Check for server ce…
I've encountered valid database schemas that do not have a primary key.  For example, I use LogParser from Microsoft to push IIS logs into a SQL database table for processing and analysis.  However, occasionally due to user error or a scheduled task…
This tutorial will teach you the special effect of super speed similar to the fictional character Wally West aka "The Flash" After Shake : http://www.videocopilot.net/presets/after_shake/ All lightning effects with instructions : http://www.mediaf…
Please read the paragraph below before following the instructions in the video — there are important caveats in the paragraph that I did not mention in the video. If your PaperPort 12 or PaperPort 14 is failing to start, or crashing, or hanging, …

610 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