Solved

SQL Query: Distinct Keyword fetches incorrect data

Posted on 2011-02-20
9
461 Views
Last Modified: 2012-05-11
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.
0
Comment
Question by:meispisces
9 Comments
 
LVL 4

Expert Comment

by:HawyLem
ID: 34936803
Use the distinct keyword for the id field and not for the value field
0
 
LVL 7

Author Comment

by:meispisces
ID: 34936814
@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
 
LVL 26

Expert Comment

by:tigin44
ID: 34936822
this should help you

select SUM(someValue )
from (
         select ID, MAX(someValue) as someValue
        from yourTable
        group by ID) A
0
Free learning courses: Active Directory Deep Dive

Get a firm grasp on your IT environment when you learn Active Directory best practices with Veeam! Watch all, or choose any amount, of this three-part webinar series to improve your skills. From the basics to virtualization and backup, we got you covered.

 
LVL 4

Expert Comment

by:HawyLem
ID: 34936823
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
 
LVL 7

Author Comment

by:meispisces
ID: 34936834
Query that i have posted is itself a nested query...
0
 
LVL 4

Expert Comment

by:HawyLem
ID: 34936839
I suppose you can nest more :D
0
 
LVL 22

Accepted Solution

by:
Thomasian earned 500 total points
ID: 34936889
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
 
LVL 50

Expert Comment

by:Lowfatspread
ID: 34938776
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
 
LVL 6

Expert Comment

by:Rajesh_mj
ID: 34958644
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

Featured Post

Windows Server 2016: All you need to know

Learn about Hyper-V features that increase functionality and usability of Microsoft Windows Server 2016. Also, throughout this eBook, you’ll find some basic PowerShell examples that will help you leverage the scripts in your environments!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

In this article I will describe the Detach & Attach method as one possible migration process and I will add the extra tasks needed for an upgrade when and where is applied so it will cover all.
This article describes how to use the timestamp of existing data in a database to allow Tableau to calculate the prior work day instead of relying on case statements or if statements to calculate the days of the week.
Although Jacob Bernoulli (1654-1705) has been credited as the creator of "Binomial Distribution Table", Gottfried Leibniz (1646-1716) did his dissertation on the subject in 1666; Leibniz you may recall is the co-inventor of "Calculus" and beat Isaac…

821 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