Solved

SQL Distinct

Posted on 2014-01-23
2
601 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
2 Comments
 
LVL 4

Expert Comment

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

Accepted Solution

by:
pcelba earned 500 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

PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

Question has a verified solution.

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

Creating and Managing Databases with phpMyAdmin in cPanel.
Load balancing is the method of dividing the total amount of work performed by one computer between two or more computers. Its aim is to get more work done in the same amount of time, ensuring that all the users get served faster.
This video shows how to set up a shell script to accept a positional parameter when called, pass that to a SQL script, accept the output from the statement back and then manipulate it in the Shell.
Via a live example, show how to setup several different housekeeping processes for a SQL Server.

943 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

Need Help in Real-Time?

Connect with top rated Experts

5 Experts available now in Live!

Get 1:1 Help Now