SQL Server: Using Distinct Keyword in SQL Server Query fetches incorrect data

I have one table that has following columns.

ID   Amount
1    开发者_如何学C  300
2      400
3      500
1      300 
2      400
3      500

Corresponding to IDs there is amount column.

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

ID   Amount
1      300
2      400
3      500
4      400 

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?

FYI, my SQL Query is not just about the summation, but it also involves relationship with other tables and then using some formula, I am bringing up the data.

Thanks

UPDATED: SQL QUERY ADDED

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

Using your initial example, you can correctly calculate the sum, if, when selecting distinct rows, you supply the amount value with the corresponding ID. That way you will select rows with identical sums, but not exact duplicates.

SELECT SUM(Amount)
FROM (
  SELECT DISTINCT
    ID,
    Amount
  FROM YourTable
) s

In the added query I think you need to modify this subselect:

select distinct billdetail.fees
from billdetail
  join payment on billdetail.billdetailid = payment.billdetailid
where billdetail.patientid=@patientid

like this:

select distinct billdetail.ID, billdetail.fees
from billdetail
  join payment on billdetail.billdetailid = payment.billdetailid
where billdetail.patientid=@patientid

That is, by adding billdetail.ID you provide the necessary level of distinction, so the resulting sum should be correct.

Try this (untested but notice the "SUM(DISTINCT ...)")

SELECT SUM(DISTINCT  billdetail.fees) - 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 ;

Please try to format queries properly for easy comprehension.

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

I'm going to assume that you simply wanted distinct fees from billdetail vs sum of payment records. It is strange to use [inner] JOIN in the first part for fees, and LEFT for payments (when the right side is where the values come from).

Select sum(f) from
(
Select
        fees
       -
        isnull((Select isnull(SUM (Payment.PlanPaid),0) +
                isnull(SUM (Payment.PatPaid),0) + 
                isnull(SUM (Payment.WriteOff1),0) +
                isnull(SUM (Payment.WriteOff2),0)
        from  Payment 
        where BillDetail.BillDetailID = Payment.BillDetailID),0) f
from BillDetail
where PatientID = @patientid
) X