Interfering COUNT functions - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

Interfering COUNT functions

Author

Topic

dneil
Starting Member

12 Posts

Posted - 2009-12-11 : 06:53:30

Hello all,

I work for a company who receive clients and applicants from numerous sources. I am trying to create a query that will give me the following result set:

Source Applicants Clients
Google 3 20
Friend 6 3

...etc. I didn't expect this to be a problem, but I can't seem to combine the following two individual queries without something interfering:

-----------------------------------------------------------

SELECT
dbo.Sources.Description,
COUNT (distinct dbo.Applicants.ApplicantId) AS 'Tutor Referrals'

FROM dbo.Sources
RIGHT OUTER JOIN dbo.Applicants ON
dbo.Sources.SourceID=dbo.Applicants.SourceId

GROUP BY dbo.Sources.Description

-----------------------------------------------------------

SELECT
dbo.Sources.Description,
COUNT (distinct dbo.Clients.ClientId) AS 'Client Referrals'

FROM dbo.Sources
RIGHT OUTER JOIN dbo.Clients ON
dbo.Sources.SourceID=dbo.Clients.SourceId

GROUP BY dbo.Sources.Description

-----------------------------------------------------------

The results these two produce are perfect, they are the numbers I need, but when I attempt to combine them into one result set something interferes and the query takes upwards of 4 minutes when the two individual ones take a second.

There are two separate tables for Applicants and Clients, each with a 'SourceId' column corresponding to the 'SourceId' in a separate 'Sources' table which contains the source name.

What is the best way to combine these two?

Any help greatly appreciated.

Thanks.

RyanRandall
Master Smack Fu Yak Hacker

1074 Posts

Posted - 2009-12-11 : 08:31:12

Here's something to play around with...

declare @Sources table (SourceId int, Description varchar(50))
insert @Sources
          select 1, 'Google'
union all select 2, 'Friend'

declare @Applicants table (ApplicantId int identity(1, 1), SourceId int)
insert @Applicants
          select 1 union all select 1 union all select 1
union all select 2 union all select 2 union all select 2
union all select 2 union all select 2 union all select 2

declare @Clients table (ClientId int identity(1, 1), SourceId int)
insert @Clients
          select 1 union all select 1 union all select 1
union all select 1 union all select 1 union all select 1
union all select 1 union all select 1 union all select 1
union all select 1 union all select 1 union all select 1
union all select 1 union all select 1 union all select 1
union all select 1 union all select 1 union all select 1
union all select 1 union all select 1
union all select 2 union all select 2 union all select 2

select a.Description, Count(b.SourceId) as Count
from @Sources a left outer join @Applicants b on a.SourceId = b.SourceId group by a.SourceId, a.Description
order by a.SourceId

select a.Description, Count(b.SourceId) as Count
from @Sources a left outer join @Clients b on a.SourceId = b.SourceId group by a.SourceId, a.Description
order by a.SourceId

select a.Description,
    sum(case when Category = 'Applicant' then 1 else 0 end) as Applicants,
    sum(case when Category = 'Client' then 1 else 0 end) as Clients,
    Count(b.SourceId) as Total
from @Sources a left outer join
    (select 'Applicant' as Category, * from @Applicants union all select 'Client', * from @Clients) b
    on a.SourceId = b.SourceId group by a.SourceId, a.Description
order by a.SourceId

select a.Description, a.Count as Applicants, b.Count as Clients, a.Count + b.Count as Total
from (
    select a.SourceId, a.Description, Count(b.SourceId) as Count
    from @Sources a left outer join @Applicants b on a.SourceId = b.SourceId group by a.SourceId, a.Description) a
inner join (
    select a.SourceId, a.Description, Count(b.SourceId) as Count
    from @Sources a left outer join @Clients b on a.SourceId = b.SourceId group by a.SourceId, a.Description) b
on a.SourceId = b.SourceId
order by a.SourceId

Ryan Randall - Yak of all trades
Solutions are easy. Understanding the problem, now, that's the hard part.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2009-12-11 : 12:15:51

[code]SELECT
dbo.Sources.Description,
COUNT (distinct CASE WHEN tmp.Category = 'Applicant' THEN tmp.Id ELSE NULL END) AS 'Tutor Referrals',
COUNT (distinct CASE WHEN tmp.Category = 'Client' THEN tmp.Id ELSE NULL END) AS 'Client Referrals'
FROM dbo.Sources
RIGHT OUTER JOIN
(SELECT SourceId,ApplicantId AS Id,'Applicant' AS Category
FROM dbo.Applicants
UNION ALL
SELECT SourceId,ClientId,'Client'
FROM dbo.Applicants
)tmp
ON
dbo.Sources.SourceID=tmp.SourceId
GROUP BY dbo.Sources.Description
[/code]

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2009-12-12 : 01:50:21

quote:

Originally posted by visakh16

SELECT
dbo.Sources.Description,
COUNT (distinct CASE WHEN  tmp.Category = 'Applicant' THEN tmp.Id ELSE NULL END) AS 'Tutor Referrals',
COUNT (distinct CASE WHEN  tmp.Category = 'Client' THEN tmp.Id ELSE NULL END) AS 'Client Referrals'
FROM dbo.Sources
RIGHT OUTER JOIN 
(SELECT SourceId,ApplicantId AS Id,'Applicant' AS Category
FROM dbo.Applicants 
UNION ALL
SELECT SourceId,ClientId,'Client' 
FROM dbo.Applicants
)tmp
ON
dbo.Sources.SourceID=tmp.SourceId
GROUP BY dbo.Sources.Description

It should be

SELECT
dbo.Sources.Description,
COUNT (distinct CASE WHEN tmp.Category = 'Applicant' THEN tmp.Id ELSE NULL END) AS 'Tutor Referrals',
COUNT (distinct CASE WHEN tmp.Category = 'Client' THEN tmp.Id ELSE NULL END) AS 'Client Referrals'
FROM dbo.Sources
RIGHT OUTER JOIN
(SELECT SourceId,ApplicantId AS Id,'Applicant' AS Category
FROM dbo.Applicants
UNION ALL
SELECT SourceId,ClientId,'Client'
FROM dbo.Clients
)tmp
ON
dbo.Sources.SourceID=tmp.SourceId
GROUP BY dbo.Sources.Description

Madhivanan

Failing to plan is Planning to fail

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2009-12-12 : 02:27:46

nice catch Madhi

dneil
Starting Member

12 Posts

Posted - 2009-12-18 : 05:22:56

Thank you so much for your help. That last solution works just perfectly.

I can quite safely say I was never going to figure that out.

Can someone give me a brief explanation of how those two COUNT lines function and how 'tmp' is used?

Thanks again guys.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2009-12-20 : 02:51:56

quote:
Originally posted by dneil

Thank you so much for your help. That last solution works just perfectly.

I can quite safely say I was never going to figure that out.

Can someone give me a brief explanation of how those two COUNT lines function and how 'tmp' is used?

Thanks again guys.

tmp is just a name (alias) given to derived table. Inside derived table we combine the details from client and applicant tables with a derived column to identify the type of data. then we group data by common field Description and then use derived column to identify count of each type.the case when inside count enables it to count only correct data and ignore others

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources