By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
446,145 Members | 1,669 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 446,145 IT Pros & Developers. It's quick & easy.

Query, need to count unique entries?

P: 1
I've inherited a database system designed to calculate sickness absence statistics. There is a main table with an employee ID [EMPLOYEEID] which also holds the number of days worked by that person in a year [thedaysworked]. There is then a table which records absences for each employee, it records the date of the absence etc and also holds the number of days absent [Number_of_days_absent]. Each time the employee is absent they will have an entry in this table linked by [EMPLOYEEID]. I have created a query which needs to total all these absence days, which works fine, however, what i need is to then total these by school and section for absences within the last year, i have also got this working without much effort.

Expand|Select|Wrap|Line Numbers
  1.  
  2. SELECT School.[Type Description] AS School, Section.[Type Description] AS [Section], Sum(absence.Working_Days_Absent) AS [Working Days of Absence]
  3. FROM [Employee Status] INNER JOIN ([Section] INNER JOIN (School INNER JOIN (Role INNER JOIN (KCSTAFF02 INNER JOIN absence ON KCSTAFF02.EMPLOYEEID = absence.employeeID) ON Role.RoleID = KCSTAFF02.ABSENCE_Role) ON School.School = KCSTAFF02.ABSENCE_School) ON Section.Section = KCSTAFF02.ABSENCE_Section) ON [Employee Status].[Employee Status] = KCSTAFF02.ABSENCE_Status
  4. WHERE (((absence.[1st_day_of_absence])>Date()-365))
  5. GROUP BY School.[Type Description], Section.[Type Description];
  6.  
The problem arises when i ask it to work out how many people are in each school and section. I asked it to count [EMPLOYEEID] from the main table but this results in this ID being counted for each entry in the absence table, which results in a lot more people than expected! I also have the same problem with [thedaysworked] which adds itself for each entry in the absence databse, so if an employee has 5 absences this gets counted 5 times.Is there a way i can tell it just to count it once but still leave the other totals which are working alone?

This is what i currently have

Expand|Select|Wrap|Line Numbers
  1.  
  2. SELECT School.[Type Description] AS School, Section.[Type Description] AS [Section], Sum(KCSTAFF02.thedaysworked) AS [Combined Days], Count(KCSTAFF02.EMPLOYEEID) AS [Employee Count], Sum(absence.Number_of_Days_Absent) AS [Days of Absence], Sum(absence.Working_Days_Absent) AS [Working Days of Absence]
  3. FROM [Employee Status] INNER JOIN ([Section] INNER JOIN (School INNER JOIN (Role INNER JOIN (KCSTAFF02 INNER JOIN absence ON KCSTAFF02.EMPLOYEEID = absence.employeeID) ON Role.RoleID = KCSTAFF02.ABSENCE_Role) ON School.School = KCSTAFF02.ABSENCE_School) ON Section.Section = KCSTAFF02.ABSENCE_Section) ON [Employee Status].[Employee Status] = KCSTAFF02.ABSENCE_Status
  4. WHERE (((absence.[1st_day_of_absence])>Date()-365))
  5. GROUP BY School.[Type Description], Section.[Type Description];
  6.  
Dec 11 '07 #1
Share this Question
Share on Google+
1 Reply


Rabbit
Expert Mod 10K+
P: 12,392
You won't be able to do both a unique count and a non-unique in the same query.

You'll have to separate them. And to do a unique count, you'll need to do a SELECT DISTINCT first.
Dec 11 '07 #2

Post your reply

Sign in to post your reply or Sign up for a free account.