SQL Query: Distinct Keyword fetches incorrect data

Hi,

i have one table that has following columns.

ID
1
2
3
1
2
3

Corresponding to IDs there is amount column.
Hence for ID 1, amount is 300, 2 is 400 and 3 is 500. I wish to sum the amount column for which i am using the distinct keyword so that the sum is 1200 and not 2400.

But here is the catch;

Say if ID is
1
2
3
4

and the values are 300, 400, 500 and 400. Hence the total should be 1600, but because i have used DISTINCT keyword for case1, the total comes to 1200.

How should i write my sql query so that both the case are satisfy?

Inputs appreciated.
LVL 7
meispiscesAsked:
Who is Participating?
 
ThomasianConnect With a Mentor Commented:
meispisces,

In your 1st scenario where the IDs are duplicated, will the values for each Id be always the same? e.g. If there are 2 records with ID=1, then is the amount for both records also the same?

If it is, then you could include the id in the distinct and ignore it when getting the sum.
i.e.

(select sum( fees) from (select distinct  id, billdetail.fees from billdetail  
       join payment on billdetail.billdetailid = payment.billdetailid  
       where billdetail.patientid=@patientid) as temp)

If not, how do you determine which of the 2 records you are going to use?
0
 
HawyLemCommented:
Use the distinct keyword for the id field and not for the value field
0
 
meispiscesAuthor Commented:
@HawyLem: But i am not using ID field in my Select Query.

Attached is my Query. The bold distinct is the problem.


Select
distinct
((select sum( fees) from (select distinct  billdetail.fees from billdetail  
       join payment on billdetail.billdetailid = payment.billdetailid  
       where billdetail.patientid=@patientid) as temp)
       -
     (Select SUM (Payment.PlanPaid) + SUM (Payment.PatPaid) + SUM (Payment.WriteOff1) + SUM (Payment.WriteOff2)

from  
BillDetail
left outer join Payment on BillDetail.BillDetailID = Payment.BillDetailID
where BillDetail.PatientID = @patientid))
from BillDetail
where PatientID = @patientid
0
Get expert help—faster!

Need expert help—fast? Use the Help Bell for personalized assistance getting answers to your important questions.

 
tigin44Commented:
this should help you

select SUM(someValue )
from (
         select ID, MAX(someValue) as someValue
        from yourTable
        group by ID) A
0
 
HawyLemCommented:
First-fast idea: select using distinct id in your query, then make the entire query an inner query of another where you select only the fields you're interested in

(http://www.sql-tutorial.com/sql-nested-queries-sql-tutorial/)
0
 
meispiscesAuthor Commented:
Query that i have posted is itself a nested query...
0
 
HawyLemCommented:
I suppose you can nest more :D
0
 
LowfatspreadCommented:
either you have over simplified the situation or your table design is a mess...

take a step back and explain how you end up with "duplicate" rows on your table...

what is the actual scenario?

surely the data is "grouped" into some sort of "Set" for the transactions/values....

you've now come across the scenario where to of the id's can have the same amount...
what other scenarios aren't you thinking of at present?

(e.g. what would happend if an id was supposed to be deleted ...)

if would be best if you provided the actual background to your problem, and the details
of what you are supposed to be selecting... to achieve your summation...

e.g. I need to obtain the total of a set of line items for an "invoice/order"...
  i need to obtain the details from the "latest/at a point in time/..."


0
 
Rajesh_mjCommented:
Please check the below code can give some thing near to ur expectation.
Select sum(B1.fees) -  (Select SUM (P.PlanPaid) + SUM (P.PatPaid) + SUM (P.WriteOff1) + SUM (P.WriteOff2) as paid
FROM BillDetail B2 LEFT OUTER JOIN P P on B2.BillDetailID = P.BillDetailID
where B2.PatientID = B1.PatientID )
from BillDetail B1
where B1.PatientID = @patientid

Open in new window

0
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.

All Courses

From novice to tech pro — start learning today.