jcook32
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.Location ID,DATEPAR T(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.Employee ID
WHERE (EmployeeTitles.TitleID IN (4, 12, 14, 37)) AND (EmployeeLocations.Locatio nID = 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.Employee ID
WHERE (EmployeeTitles.TitleID IN (4, 12, 14, 37)) AND (EmployeeLocations.Locatio nID = 40)
AND (DATEPART(yy, LMS_Class.DateTimeFrom) = 2005)
is there any way to return the 24 using count.
thanks
jcook32
SELECT DISTINCT Employees.LastName + ', ' + Employees.FirstName + COALESCE (' ' + Employees.MiddleName, '') AS AttyName, Employees.EmployeeID, EmployeeLocations.Location
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.Employee
WHERE (EmployeeTitles.TitleID IN (4, 12, 14, 37)) AND (EmployeeLocations.Locatio
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.Employee
WHERE (EmployeeTitles.TitleID IN (4, 12, 14, 37)) AND (EmployeeLocations.Locatio
AND (DATEPART(yy, LMS_Class.DateTimeFrom) = 2005)
is there any way to return the 24 using count.
thanks
jcook32
ASKER
i want one record returned that gives me the amount of records in the dataset.
24 not 136 in this case
24 not 136 in this case
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
that did the trick
thankks
thankks
What exactly do you want in your results?