Help with count function - 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

SQL Server 2005 Forums

Transact-SQL (2005)

Help with count function

Author

Topic

bendertez
Yak Posting Veteran

94 Posts

Posted - 2008-07-25 : 09:56:08

Hello

I have a database holding staff course details.

I have a query that counts and returns people's course records that have a "live" status field (People can be on multiple live courses).

Here is my query:

select p.pref as [PRef], count(p.pref) as [Count]
FROM person p
LEFT JOIN enroll e on p.pref=e.eref
LEFT JOIN course c on c.cref=e.cref

WHERE e.status = "LIVE"
group by p.pref

After running this query I get a result like:

PRef......Count
0234...... 1
0456...... 3
0466...... 2
0566...... 1
0677...... 5
0766...... 2

What I want to do is return the number of people that do not have ANY live courses running at the moment (remember people can have a live and completed courses).

I thought that I must be able to use the above query and stick an = 0 condition somewhere but i'm struggling how to construct it.

Can anybody help....there must be a simple way of doing this.

Thanks

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2008-07-25 : 09:59:00

select p.pref as [PRef], count(p.pref) as [Count]
FROM person p
LEFT JOIN enroll e on p.pref=e.eref and e.status = "LIVE"
LEFT JOIN course c on c.cref=e.cref
WHERE e.eref is null
group by p.pref

KH
[spoiler]Time is always against us[/spoiler]

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-07-25 : 10:31:13

minor variation

select p.pref as [PRef], count(p.pref) as [Count]
FROM person p
LEFT JOIN enroll e on p.pref=e.eref
LEFT JOIN course c on c.cref=e.cref
group by p.pref
HAVING SUM(CASE WHEN  e.status = "LIVE" THEN 1 ELSE 0 END)=0

bendertez
Yak Posting Veteran

94 Posts

Posted - 2008-07-25 : 10:42:59

Hi

Thanks for the reply, however it doesn't seem to give the required output.

I just want to know all the people who do not have any live courses currently ongoing.

I was presuming that as it was a count query I would need to return the people who's live status count was = 0.

Is this not the case?

Tahnks again

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-07-25 : 10:48:38

quote:
Originally posted by bendertez

Hi

Thanks for the reply, however it doesn't seem to give the required output.

I just want to know all the people who do not have any live courses currently ongoing.

I was presuming that as it was a count query I would need to return the people who's live status count was = 0.

Is this not the case?

Tahnks again

But wont those peole having live courses have a record in enroll table with status LIVE?

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources