[Webinar] Streamline your web hosting managementRegister Today

x
Solved

# better way to get a count

Posted on 2008-10-13
Medium Priority
240 Views
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
Question by:dgrafx
• 4
• 4
• 4
• +1

LVL 17

Expert Comment

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
``````
0

LVL 17

Expert Comment

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
``````
0

LVL 25

Author Comment

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 61

Expert Comment

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'
``````
0

LVL 61

Expert Comment

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
``````
0

LVL 17

Expert Comment

ID: 22708322
0

LVL 17

Accepted Solution

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
``````
0

LVL 5

Expert Comment

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
``````
0

LVL 61

Expert Comment

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

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

ID: 31505766
this one seemed to work out the best and thanks to everyone
0

LVL 61

Expert Comment

ID: 22710424
LOL.

Regards,
Kevin
0

LVL 25

Author Comment

ID: 22710435
thanks Kevin
0

## Featured Post

Question has a verified solution.

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

In this article I will describe the Copy Database Wizard method as one possible migration process and I will add the extra tasks needed for an upgrade when and where is applied so it will cover all.
Use this article to create a batch file to backup a Microsoft SQL Server database to a Windows folder.  The folder can be on the local hard drive or on a network share.  This batch file will query the SQL server to get the current date & time and wi…
SQL Database Recovery Software repairs the MDF & NDF Files, corrupted due to hardware related issues or software related errors. Provides preview of recovered database objects and allows saving in either MSSQL, CSV, HTML or XLS format. Ensures recov…
Stellar Phoenix SQL Database Repair software easily fixes the suspect mode issue of SQL Server database. It is a simple process to bring the database from suspect mode to normal mode. Check out the video and fix the SQL database suspect mode problem.
###### Suggested Courses
Course of the Month8 days, 3 hours left to enroll