SQL Distinct

I'm getting duplicate records running this query and would like to eliminate the client name duplicates.

Was trying to do a distinct but still results had duplicates.
----------------------------------------------------------------------------------------------------------------------------
SELECT    DISTINCT Clients.ClientCode, Clients.LastName, Clients.FirstName, Clients.DateOfBirth, Clients.Disability, Clients.PrefSpaceType, Clients.EligFromDate, Clients.EligToDate,  ClientStats.TripCount

FROM         Clients INNER JOIN
                      ClientStats AS ClientStats ON Clients.ClientId = ClientStats.ClientId
WHERE     (Clients.InActive = 0) AND (Clients.PrefSpaceType = 'SC' OR
                      Clients.PrefSpaceType = 'ST' OR
                      Clients.PrefSpaceType = 'XL' OR
                      Clients.PrefSpaceType = 'XW')
ORDER BY Clients.LastName
bjbrownAsked:
Who is Participating?
 
pcelbaConnect With a Mentor Commented:
You should aggregate the trip count:

SELECT  Clients.ClientCode, Clients.LastName, Clients.FirstName, Clients.DateOfBirth, Clients.Disability, Clients.PrefSpaceType, Clients.EligFromDate, Clients.EligToDate,  SUM(ClientStats.TripCount) AS TripCount
FROM         Clients INNER JOIN
                      ClientStats AS ClientStats ON Clients.ClientId = ClientStats.ClientId
WHERE     (Clients.InActive = 0) AND (Clients.PrefSpaceType = 'SC' OR
                      Clients.PrefSpaceType = 'ST' OR
                      Clients.PrefSpaceType = 'XL' OR
                      Clients.PrefSpaceType = 'XW')
GROUP BY Clients.ClientCode, Clients.LastName, Clients.FirstName, Clients.DateOfBirth, Clients.Disability, Clients.PrefSpaceType, Clients.EligFromDate, Clients.EligToDate
ORDER BY Clients.LastName
0
 
ravikantninaveCommented:
Why don't you use group by clause if possible
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.