[Last Call] Learn how to a build a cloud-first strategyRegister Now

x
?
Solved

SQL aggregate function SUM not working

Posted on 2014-08-02
13
Medium Priority
?
1,467 Views
Last Modified: 2014-08-04
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.
0
Comment
Question by:rrhandle8
  • 3
  • 3
  • 2
  • +3
13 Comments
 
LVL 120

Expert Comment

by:Rey Obrero (Capricorn1)
ID: 40236936
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
 
LVL 31

Expert Comment

by:hnasr
ID: 40236971
Try:
Select Doctor, Count(pationtID) As [Patients count] , Sum(Payment) As [Total Payment]
GROUP By Doctor;
0
 
LVL 15

Expert Comment

by:gplana
ID: 40237077
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
Industry Leaders: 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!

 
LVL 31

Expert Comment

by:hnasr
ID: 40237140
If you list few records, and the expected output, then the problem will be clear.
That will stop, if ... and if ...
0
 
LVL 15

Expert Comment

by:gplana
ID: 40237145
if there are more than 1 record for patient X, then just add a GROUP BY patient
0
 

Author Comment

by:rrhandle8
ID: 40237513
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
 
LVL 15

Expert Comment

by:gplana
ID: 40237539
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
 
LVL 120

Expert Comment

by:Rey Obrero (Capricorn1)
ID: 40238061
@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
 
LVL 31

Expert Comment

by:hnasr
ID: 40238087
"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
 
LVL 49

Expert Comment

by:PortletPaul
ID: 40238115
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
 
LVL 52

Accepted Solution

by:
Gustav Brock earned 2000 total points
ID: 40238458
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
 

Author Closing Comment

by:rrhandle8
ID: 40239431
Gustav, you nailed it!
0
 
LVL 52

Expert Comment

by:Gustav Brock
ID: 40239573
You are welcome!

/gustav
0

Featured Post

Independent Software Vendors: 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

Access developers frequently have requirements to interact with Excel (import from or output to) in their applications.  You might be able to accomplish this with the TransferSpreadsheet and OutputTo methods, but in this series of articles I will di…
Instead of error trapping or hard-coding for non-updateable fields when using QODBC, let VBA automatically disable them when forms open. This way, users can view but not change the data. Part 1 explained how to use schema tables to do this. Part 2 h…
Visualize your data even better in Access queries. Given a date and a value, this lesson shows how to compare that value with the previous value, calculate the difference, and display a circle if the value is the same, an up triangle if it increased…
Look below the covers at a subform control , and the form that is inside it. Explore properties and see how easy it is to aggregate, get statistics, and synchronize results for your data. A Microsoft Access subform is used to show relevant calcul…
Suggested Courses

830 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