Solved

SQL Query: Distinct Keyword fetches incorrect data

Posted on 2011-02-20
9
454 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
Optimizing Cloud Backup for Low Bandwidth

With cloud storage prices going down a growing number of SMBs start to use it for backup storage. Unfortunately, business data volume rarely fits the average Internet speed. This article provides an overview of main Internet speed challenges and reveals backup best practices.

 
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

Microsoft Certification Exam 74-409

Veeam® is happy to provide the Microsoft community with a study guide prepared by MVP and MCT, Orin Thomas. This guide will take you through each of the exam objectives, helping you to prepare for and pass the examination.

Question has a verified solution.

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

In this article I will describe the Copy Database Wizard 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.
Composite queries are used to retrieve the results from joining multiple queries after applying any filters. UNION, INTERSECT, MINUS, and UNION ALL are some of the operators used to get certain desired results.​
This tutorial gives a high-level tour of the interface of Marketo (a marketing automation tool to help businesses track and engage prospective customers and drive them to purchase). You will see the main areas including Marketing Activities, Design …
This video shows how to use Hyena, from SystemTools Software, to bulk import 100 user accounts from an external text file. View in 1080p for best video quality.

776 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