Link to home
Start Free TrialLog in
Avatar of jcook32
jcook32Flag for United States of America

asked on

problem returning correct data from a table when using count

first query return 24 rows:
SELECT DISTINCT   Employees.LastName + ', ' + Employees.FirstName + COALESCE (' ' + Employees.MiddleName, '') AS AttyName, Employees.EmployeeID,  EmployeeLocations.LocationID,DATEPART(yy, LMS_Class.DateTimeFrom)
FROM         Titles INNER JOIN
                      EmployeeTitles ON Titles.TitleID = EmployeeTitles.TitleID INNER JOIN
                      LMS_Student INNER JOIN
                      LMS_Class ON LMS_Student.ClassID = LMS_Class.ClassID INNER JOIN
                      LMS_Courses ON LMS_Class.CourseID = LMS_Courses.CourseID INNER JOIN
                      Employees ON LMS_Student.EmployeeID = Employees.EmployeeID ON EmployeeTitles.EmployeeID = Employees.EmployeeID INNER JOIN
                      EmployeeLocations ON LMS_Student.EmployeeID = EmployeeLocations.EmployeeID
WHERE     (EmployeeTitles.TitleID IN (4, 12, 14, 37)) AND (EmployeeLocations.LocationID = 40)
AND (DATEPART(yy, LMS_Class.DateTimeFrom) = 2005)

second return 136 (which is the total rows of the first if you removed the distinct

SELECT DISTINCT Count(Employees.EmployeeID)

FROM         Titles INNER JOIN
                      EmployeeTitles ON Titles.TitleID = EmployeeTitles.TitleID INNER JOIN
                      LMS_Student INNER JOIN
                      LMS_Class ON LMS_Student.ClassID = LMS_Class.ClassID INNER JOIN
                      LMS_Courses ON LMS_Class.CourseID = LMS_Courses.CourseID INNER JOIN
                      Employees ON LMS_Student.EmployeeID = Employees.EmployeeID ON EmployeeTitles.EmployeeID = Employees.EmployeeID INNER JOIN
                      EmployeeLocations ON LMS_Student.EmployeeID = EmployeeLocations.EmployeeID
WHERE     (EmployeeTitles.TitleID IN (4, 12, 14, 37)) AND (EmployeeLocations.LocationID = 40)
AND (DATEPART(yy, LMS_Class.DateTimeFrom) = 2005)


is there any way to return the 24 using count.

thanks

jcook32
Avatar of Atlanta_Mike
Atlanta_Mike

Yes, but what is the COLUMN that's causing the duplicate records?  Youneed to group by and max, min or count the column that's causing the issue.

What exactly do you want in your results?
Avatar of jcook32

ASKER

i want one record returned that gives me the amount of records in the dataset.
24 not 136 in this case
ASKER CERTIFIED SOLUTION
Avatar of gena17
gena17

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Avatar of jcook32

ASKER

that did the trick

thankks