SQL aggregate function SUM not working

I am trying to get a list of all visits to the doctor by patient X and the sum of all payments.
If two visits, first one $80 and second one $100, then there should be two records return and the sum field should = 180, but instead I get the sum on the first record as $80 and the second as $100.
rrhandle8Asked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Rey Obrero (Capricorn1)Commented:
you probably have added a field in the group by clause that have different values.

try something like this

select [patient], sum(payment) as totalPayment
from tablex
where patient="x"
group by [patient]

if the patient have visited the same doctor, you can include the doctor/s field in the query


select [patient], [doctor],sum(payment) as totalPayment
from tablex
where patient="x"
group by  [patient], [doctor]
0
hnasrCommented:
Try:
Select Doctor, Count(pationtID) As [Patients count] , Sum(Payment) As [Total Payment]
GROUP By Doctor;
0
gplanaCommented:
I think the correct answer is a mix of both previous solutions:

SELECT Patient, Count(doctorId) as HowManyDoctors, Sum(Payment) as TotalPayment
FROM TableX
GROUP BY patient

Open in new window

;

But if you want just to know this for just a patient (patient X) then do this:

SELECT Count(doctorId) as HowManyDoctors, Sum(Payment) as TotalPayment
FROM TableX
WHERE patient='X';

Open in new window


Hope it helps. Regards.
0
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

hnasrCommented:
If you list few records, and the expected output, then the problem will be clear.
That will stop, if ... and if ...
0
gplanaCommented:
if there are more than 1 record for patient X, then just add a GROUP BY patient
0
rrhandle8Author Commented:
Can someone confirm my suspicions here.  I think the query below is impossible.  Notice I add payment to Rey's example.

select [patient], payment, sum(payment) as totalPayment
from tablex
where patient="x"
group by [patient]
0
gplanaCommented:
is not right. How can you have payment field and also sum(payment) ?
If there is more than a value for payment field for a single patient then which value should system show?What do you want to achieve by adding payment field? You can add its minimum value, its maximum value, the sum of values, .... but not THE value ias there can be many.

In case of MySQL it doesn't arise an error and it shows just one value (the first, normally) but this is not the standard behaviour
0
Rey Obrero (Capricorn1)Commented:
@rrhandle8

your SQL query

select [patient], payment, sum(payment) as totalPayment
 from tablex
 where patient="x"
 group by [patient]

will not even run, it will ask for aggregation of the field [payment] you added to the SQL.
0
hnasrCommented:
"Can someone confirm ..."

Compare with this syntax:

SELECT fld1, fld2, fld3, Sum(fld4)
From tbl1
GROUP BY fld1, fld2, fld3;


If you add another field, you have to include it in the Group By Expression.

SELECT fld0, fld1, fld2, fld3, Sum(fld4)
From tbl1
GROUP BY fld0, fld1, fld2, fld3;

The order By fields can be of different order than in the Select part, depending on your requirement.

SELECT fld0, fld1, fld2, fld3, Sum(fld4)
From tbl1
GROUP BY fld1, fld0, fld3, fld2;


listing few input records and the expected output helps understanding and then presenting better comment to your issue.
0
PortletPaulfreelancerCommented:
Let's put it this way, when using "group by" there are some rules:

Everything in the select list MUST be either:
A. Fields you want to "group by"
B. Data you will aggregate

part of your query does not comply with either rule A or B so it will not work
--    query                              Rules:
---------------------------------------- --------------
SELECT
      [patient]                          A
    , [payment]                             not A or B --> wrong
    , SUM(payment) AS totalPayment       B, aggregating by sum()
FROM tablex
WHERE patient = "x"
GROUP BY
      [patient]                          A

Open in new window

PLEASE, could we stop guessing?
Provide us with some sample data, and an expected result. Use real table and field names.
If privacy is an issue just change names to John/Jane Doe or just leave out any names.

By the way, my guess is you are seeking a "running sum", but until I see the sample data and expected result that is pure conjecture.
0
Gustav BrockCIOCommented:
You want both the single payments and the sum for each patient for each payment.
You cannot Group by Patient and Payment as it would rule out multiple payments of the same amount from the same patient, thus:

select
    patient,
    payment,
        (Select Sum(payment) From tablex As T Where T.Patient = tablex.Patient) As
    totalPayment
from
    tablex
where
    patient="x"

/gustav
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
rrhandle8Author Commented:
Gustav, you nailed it!
0
Gustav BrockCIOCommented:
You are welcome!

/gustav
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Access

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.