• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 667
  • Last Modified:

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
0
bjbrown
Asked:
bjbrown
1 Solution
 
ravikantninaveCommented:
Why don't you use group by clause if possible
0
 
pcelbaCommented:
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

Featured Post

Free Tool: Path Explorer

An intuitive utility to help find the CSS path to UI elements on a webpage. These paths are used frequently in a variety of front-end development and QA automation tasks.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now