Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

SQL Query: Distinct Keyword fetches incorrect data

Posted on 2011-02-20
9
Medium Priority
?
482 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
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
Survive A High-Traffic Event with Percona

Your application or website rely on your database to deliver information about products and services to your customers. You can’t afford to have your database lose performance, lose availability or become unresponsive – even for just a few minutes.

 
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 2000 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

Fill in the form and get your FREE NFR key NOW!

Veeam® is happy to provide a FREE NFR server license to certified engineers, trainers, and bloggers.  It allows for the non‑production use of Veeam Agent for Microsoft Windows. This license is valid for five workstations and two servers.

Question has a verified solution.

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

Ever needed a SQL 2008 Database replicated/mirrored/log shipped on another server but you can't take the downtime inflicted by initial snapshot or disconnect while T-logs are restored or mirror applied? You can use SQL Server Initialize from Backup…
This post looks at MongoDB and MySQL, and covers high-level MongoDB strengths, weaknesses, features, and uses from the perspective of an SQL user.
Video by: ITPro.TV
In this episode Don builds upon the troubleshooting techniques by demonstrating how to properly monitor a vSphere deployment to detect problems before they occur. He begins the show using tools found within the vSphere suite as ends the show demonst…
This tutorial will teach you the special effect of super speed similar to the fictional character Wally West aka "The Flash" After Shake : http://www.videocopilot.net/presets/after_shake/ All lightning effects with instructions : http://www.mediaf…

705 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