Question regarding count() - 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

Question regarding count()

Author

Topic

opi
Starting Member

29 Posts

Posted - 2008-05-27 : 15:16:22

Hello,

I have a question regarding count. I have a stored procedure that gets some information about a profile from several tables. But now i also want to count something. I want to count amount of contracts a profile has which are also of status 0 (open). Now it's possible that not a single profile has an open contract at the moment. The problem is when this occurs, then I don't get a single result..I just want 0 to display in this situation.

Can anyone help me ?

By the way, do I have to specify every field after group by. If I don't do this, I get errors..

The procedure looks like this:
create procedure [dbo].[sp_Select_All_ProfileViews]
as
select
p.ProfileId,
p.ProfileFirstName,
p.ProfileLastName,
p.ProfileCity,
t.TitleName,
r.ResumeExperienceYears,
r.ResumeContractStatus,
count(pc.ContractId) as OpenContracts
from
Profiles p inner join Resumes r
on
p.ProfileId = r.ProfileId
inner join
Titles t
on
t.TitleId = r.TitleId
inner join
ProfileContracts pc
on
pc.ProfileId = r.ResumeId
inner join
Contracts c
on
c.ContractId = p.ProfileId
where
c.ContractStatus = 0
Group By p.ProfileId,p.ProfileFirstName,p.ProfileLastName,
p.ProfileCity,t.TitleName,r.ResumeExperienceYears,r.ResumeContractStatus
Order By p.ProfileLastName,p.ProfileFirstName;

THANX in advance

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-05-28 : 00:24:02

May be this

create procedure [dbo].[sp_Select_All_ProfileViews]
as
select
p.ProfileId,
p.ProfileFirstName,
p.ProfileLastName,
p.ProfileCity,
t.TitleName,
r.ResumeExperienceYears,
r.ResumeContractStatus,
count(case when c.ContractId is not null then pc.ContractId else null end) as OpenContracts
from Profiles p inner join Resumes r
on p.ProfileId = r.ProfileId 
inner join Titles t
on t.TitleId = r.TitleId
left outer join  ProfileContracts pc
on pc.ProfileId = r.ResumeId
left outer join Contracts c
on c.ContractId = p.ProfileId
and c.ContractStatus = 0 Group By p.ProfileId,p.ProfileFirstName,p.ProfileLastName,
p.ProfileCity,t.TitleName,r.ResumeExperienceYears,r.ResumeContractStatus
Order By p.ProfileLastName,p.ProfileFirstName;

opi
Starting Member

29 Posts

Posted - 2008-05-28 : 03:46:58

awsome mate,
works like a charm!

Can I ask why you used a left outer join ? I use this rarely so don't really know when to use this.

This is basically some T-SQL between the count brackets, right?

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-05-28 : 03:54:39

quote:
Originally posted by opi

awsome mate,
works like a charm!

Can I ask why you used a left outer join ? I use this rarely so don't really know when to use this.

This is basically some T-SQL between the count brackets, right?

i used left join so that it returns all records from main table regardless of whether it has matching on the other tables. This will ensure the result to contain all records from main table with NULL values for other table columns if it doesnt have a matching column. ANd while taking count i just check this condition to count only those records that have a matching field in Contracts table.

opi
Starting Member

29 Posts

Posted - 2008-05-28 : 04:02:16

Thank you very much

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-05-28 : 04:05:59

quote:
Originally posted by opi

Thank you very much

You're welcome

opi
Starting Member

29 Posts

Posted - 2008-05-28 : 04:07:35

Hmm did some more testing and it seems like the count stays 0 even if there is a contract with ContractStatus 0.
What could be the problem ?

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-05-28 : 04:18:44

quote:
Originally posted by opi

Hmm did some more testing and it seems like the count stays 0 even if there is a contract with ContractStatus 0.
What could be the problem ?

Are you sure you have a non null value returned for pc.ContractId in ProfileContracts table for that record?

opi
Starting Member

29 Posts

Posted - 2008-05-28 : 04:30:10

I think so, normally a uniqueidentifier should be returned.
This is what profilecontracts looks like:

[ContractId] [uniqueidentifier] NOT NULL,
[ProfileId] [uniqueidentifier] NOT NULL,

And this is what contracts looks like:
[ContractId] [uniqueidentifier] NOT NULL,
[ContractCustomer] [nvarchar](150) COLLATE Latin1_General_CI_AS NOT NULL,
[ContractSetDate] [datetime] NOT NULL,
[ContractPeriodFrom] [datetime] NOT NULL,
[ContractPeriodUntil] [datetime] NOT NULL,
[ContractRemark] [nvarchar](250) COLLATE Latin1_General_CI_AS NULL,
[ContractSalary] [int] NOT NULL,

opi
Starting Member

29 Posts

Posted - 2008-05-28 : 04:34:30

I get that one record that has a contract with a contractstatus of 0 when I run the following query:
Don't really see whats wrong :s

select
pc.ProfileId,
count(pc.ContractId) as OpenContracts
from
ProfileContracts pc inner join Contracts c
on
c.ContractId = pc.ContractId
and
c.ContractStatus = 0
group by pc.ProfileId;

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-05-28 : 04:44:28

quote:
Originally posted by opi

I get that one record that has a contract with a contractstatus of 0 when I run the following query:
Don't really see whats wrong :s

select
pc.ProfileId,
count(pc.ContractId) as OpenContracts
from
ProfileContracts pc inner join Contracts c
on
c.ContractId = pc.ContractId and
c.ContractStatus = 0
group by pc.ProfileId;

This is not the original query you posted. there you have used the condition
on c.ContractId = p.ProfileId
instead of the one given now.

opi
Starting Member

29 Posts

Posted - 2008-05-28 : 04:51:32

My bad,

Now it works, I had to change something else:

ALTER procedure [dbo].[sp_Select_All_ProfileViews]
as
select
p.ProfileId,
p.ProfileFirstName,
p.ProfileLastName,
p.ProfileCity,
t.TitleName,
r.ResumeExperienceYears,
r.ResumeContractStatus,
count(case when c.ContractId is not null then pc.ContractId else null end) as OpenContracts
from
Profiles p inner join Resumes r
on
p.ProfileId = r.ProfileId
inner join
Titles t
on
t.TitleId = r.TitleId
left outer join
ProfileContracts pc
on
pc.ProfileId = p.ProfileId
left outer join
Contracts c
on
c.ContractId = pc.ContractId
and
c.ContractStatus = 0
Group By
p.ProfileId,p.ProfileFirstName,p.ProfileLastName,
p.ProfileCity,t.TitleName,r.ResumeExperienceYears,r.ResumeContractStatus
Order By p.ProfileLastName,p.ProfileFirstName;

Again, THANK U

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-05-28 : 05:07:08

quote:
Originally posted by opi

My bad,

Now it works, I had to change something else:

ALTER procedure [dbo].[sp_Select_All_ProfileViews]
as
select
p.ProfileId,
p.ProfileFirstName,
p.ProfileLastName,
p.ProfileCity,
t.TitleName,
r.ResumeExperienceYears,
r.ResumeContractStatus,
count(case when c.ContractId is not null then pc.ContractId else null end) as OpenContracts
from
Profiles p inner join Resumes r
on
p.ProfileId = r.ProfileId
inner join
Titles t
on
t.TitleId = r.TitleId
left outer join
ProfileContracts pc
on
pc.ProfileId = p.ProfileId
left outer join
Contracts c
on
c.ContractId = pc.ContractId
and
c.ContractStatus = 0
Group By
p.ProfileId,p.ProfileFirstName,p.ProfileLastName,
p.ProfileCity,t.TitleName,r.ResumeExperienceYears,r.ResumeContractStatus
Order By p.ProfileLastName,p.ProfileFirstName;

Again, THANK U

No probs. Feel free to post whenever you've a doubt.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources