If you want only 1 record, you will need to remove the LedgerType (otherwise you will get a SUM for each record by Ledger Type) so you could try SELECT a.PatientID ,
a.PatientFirstName ,
a.PatientLastName ,
b.dbStatusDesc ,
b.dbAddDate ,
b.LastName ,
b.dbStaffLastName ,
SUM(a.LedgerAmount)
FROM TESTTransactions a
INNER JOIN dbo.TESTReferralKPIs b ON a.PatientID = b.dbPatID
GROUP BY PatientID ,
PatientFirstName ,
PatientLastName ,
b.dbStatusDesc ,
b.dbAddDate ,
b.LastName ,
b.dbStaffLastName This will give you
PatientIDPatientFirstNamePatientLastNamedbStatusDescdbAddDateLastNamedbStaffLastName(SUM)
90003212AnitaButtonInvisalign2013-04-18 10:04:59.000GoogleHodgson5235.00
______________________________________________________________________________Never argue with an idiot; Theyll drag you down to their level and beat you with experience