Want to win a PS4? Go Premium and enter to win our High-Tech Treats giveaway. Enter to Win

x
?
Solved

SQL Distinct

Posted on 2014-01-23
2
Medium Priority
?
661 Views
Last Modified: 2014-01-23
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
Comment
Question by:bjbrown
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
2 Comments
 
LVL 4

Expert Comment

by:ravikantninave
ID: 39804302
Why don't you use group by clause if possible
0
 
LVL 43

Accepted Solution

by:
pcelba earned 2000 total points
ID: 39804346
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

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 this blog post, we’ll look at how using thread_statistics can cause high memory usage.
When trying to connect from SSMS v17.x to a SQL Server Integration Services 2016 instance or previous version, you get the error “Connecting to the Integration Services service on the computer failed with the following error: 'The specified service …
Via a live example, show how to backup a database, simulate a failure backup the tail of the database transaction log and perform the restore.
Via a live example, show how to setup several different housekeeping processes for a SQL Server.

618 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