Learn how to a build a cloud-first strategyRegister Now

x
?
Solved

problem returning correct data from a table when using count

Posted on 2006-05-25
4
Medium Priority
?
206 Views
Last Modified: 2008-02-26
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
0
Comment
Question by:jcook32
  • 2
4 Comments
 
LVL 13

Expert Comment

by:Atlanta_Mike
ID: 16761315
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?
0
 

Author Comment

by:jcook32
ID: 16761338
i want one record returned that gives me the amount of records in the dataset.
24 not 136 in this case
0
 
LVL 8

Accepted Solution

by:
gena17 earned 1000 total points
ID: 16761424
Try:

SELECT Count(DISTINCT Employees.EmployeeID)
0
 

Author Comment

by:jcook32
ID: 16761431
that did the trick

thankks
0

Featured Post

Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

In the first part of this tutorial we will cover the prerequisites for installing SQL Server vNext on Linux.
Windocks is an independent port of Docker's open source to Windows.   This article introduces the use of SQL Server in containers, with integrated support of SQL Server database cloning.
Via a live example, show how to setup several different housekeeping processes for a SQL Server.
Viewers will learn how the fundamental information of how to create a table.
Suggested Courses

810 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question