Solved

SQL Query: Distinct Keyword fetches incorrect data

Posted on 2011-02-20
9
445 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
 
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
Control application downtime with dependency maps

Visualize the interdependencies between application components better with Applications Manager's automated application discovery and dependency mapping feature. Resolve performance issues faster by quickly isolating problematic components.

 
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

PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
combining condition to rerun if failure within BEGIN..END 27 87
Oracle query output question 4 36
SQL Query 34 82
triggered use of sp_send_dbmail failure 2 23
'Between' is such a common word we rarely think about it but in SQL it has a very specific definition we should be aware of. While most database vendors will have their own unique phrases to describe it (see references at end) the concept in common …
I'm trying, I really am. But I've seen so many wrong approaches involving date(time) boundaries I despair about my inability to explain it. I've seen quite a few recently that define a non-leap year as 364 days, or 366 days and the list goes on. …
Along with being a a promotional video for my three-day Annielytics Dashboard Seminor, this Micro Tutorial is an intro to Google Analytics API data.
Internet Business Fax to Email Made Easy - With  eFax Corporate (http://www.enterprise.efax.com), you'll receive a dedicated online fax number, which is used the same way as a typical analog fax number. You'll receive secure faxes in your email, f…

896 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

Need Help in Real-Time?

Connect with top rated Experts

15 Experts available now in Live!

Get 1:1 Help Now