Using a Sum With Inner Joins - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

Using a Sum With Inner Joins

Author

Topic

invisible777
Starting Member

10 Posts

Posted - 2007-12-03 : 12:32:59

Hi,

I'm trying to figure out how to sum amounts generated by an attribute in one table, when the attribute and amount generated require some Joins....

Each referral can refer more than one patient. Each patient can have multiple visits. Each visit generates one bill (one Total_Charges)


Select Referral.First_Name, Referral.Last_Name, Billing.Total_Charges as [Revenue]
from Referral
inner join Patient on Patient.Referral_ID = Referral.Referral_ID
inner join PatientVisit on PatientVisit.Patient_ID = Patient.Patient_ID
inner join Billing on Billing.Visit_ID = PatientVisit.Visit_ID
group by sum(total_charges);

Right now this works fine, but every Referral who has sent multiple patients appears on a separate lines.

So a referral by the name of John Smith comes back like this:

John Smith 500.00
John Smith 300.00
John Smith 100.00

but I need it to come back like

John Smith 900.00

...possible? Thank you for your help!

TG
Master Smack Fu Yak Hacker

6065 Posts

Posted - 2007-12-03 : 12:37:38

Does this work?


Select Referral.First_Name, Referral.Last_Name, sum(Billing.Total_Charges) as [Revenue]
from Referral
inner join Patient on Patient.Referral_ID = Referral.Referral_ID
inner join PatientVisit on PatientVisit.Patient_ID = Patient.Patient_ID
inner join Billing on Billing.Visit_ID = PatientVisit.Visit_ID
group by Referral.First_Name, Referral.Last_Name;

Be One with the Optimizer
TG

invisible777
Starting Member

10 Posts

Posted - 2007-12-03 : 12:42:26

Works perfect!

Thanks for the help (the very quick help!!) !

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources