title: SQL Server: Using Distinct Keyword in SQL Server Query fetches incorrect data
body: <p>I have one table that has following columns.</p>
  
  <pre><code>ID   Amount
  1      300
  2      400
  3      500
  1      300 
  2      400
  3      500
  </code></pre>
  
  <p>Corresponding to IDs there is amount column. </p>
  
  <p>I wish to sum the amount column for which I am using the distinct keyword so that the sum is 1200 and not 2400.</p>
  
  <p>But here is the catch;</p>
  
  <p>Say if ID is </p>
  
  <pre><code>ID   Amount
  1      300
  2      400
  3      500
  4      400 
  </code></pre>
  
  <p>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.</p>
  
  <p>How should I write my sql query so that both the case are satisfy?</p>
  
  <p>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.</p>
  
  <p>Thanks</p>
  
  <p><strong>UPDATED: SQL QUERY ADDED</strong></p>
  
  <pre><code>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
  </code></pre>