Need Help Understanding these SQL Results

MISNole

I'm looking to modify a Crystal Report that calculates the number of staff per shift. When looking at the SQL, I noticed something just doesn't seem right. I'm copying and pasting 4 SQL statements that are essentially the same except two count (one distinct count) while the other two list the personID of the staff (one distinct list). The DISTINCT Count statement does not result in expected results. Was hoping someone could help me understand what is going on:

(select (personid) from rpt_peoplestaffingroledetail where roleid in (select roleid
from   rpt_peopleroledef where affectdash = 1) and shiftid in 
(select shiftid from rpt_staffingeventshiftdetail where driveshiftid = '623044'))

Gives: personid: 51135, 51135, 51135, 61905, 62926

Which makes sense has there are three people staffed for this shift, one with three roles.

When I perform a distinct listing:

(select distinct (personid) from rpt_peoplestaffingroledetail where roleid in 
(select roleid from rpt_peopleroledef where affectdash = 1) and shiftid in 
(select shiftid from rpt_staffingeventshiftdetail where driveshiftid = '623044'))

I get: personid: 51135, 61905, 62926

Which again seems correct, there are three person assigned to this shift (one with mulitple roles but the distinct makes it work)

So when I want to count the number of persons staffed:

(select count (personid) from rpt_peoplestaffingroledetail where roleid in 
(select roleid from rpt_peopleroledef where affectdash = 1) and shiftid in 
(select shiftid from rpt_staffingeventshiftdetail where driveshiftid = '623044'))

The result is 5. Which is logical, 3 people staffed + one person with 3 roles.

So this is where I run into trouble, I only want distinct person IDs counted:

(select distinct count (personid) from rpt_peoplestaffingroledetail where roleid in 
(select roleid from rpt_peopleroledef where affectdash = 1) and shiftid in 
(select shiftid from rpt_staffingeventshiftdetail where driveshiftid = '623044'))

And the result set here is still 5. And I believe it should be 3, but I cannot figure out why it is wrong.

Suggestions?

Szymon

select distinct count (personid) means getting a count (which is just one number) and then a distinct list of it (still one number).

You need select count (distinct personid) which means counting distinct personid.

Collected from the Internet

Please contact [email protected] to delete if infringement.

edited at
0

Comments

0 comments
Login to comment

Related