title: My query is returning duplicates
body: <p>I have written an SQL query to filter for a number of conditions, and have used distinct to find only unique records.</p>
  
  <p>Specifically, I need only for the <code>AccountID</code> field to be unique, there are multiple <code>AddressClientIDs</code> for each <code>AccountID</code>.</p>
  
  <p>The query works but is however producing some duplicates.</p>
  
  <p>Further caveats are:</p>
  
  <ol>
  <li>There are multiple trans for each <code>AccountID</code></li>
  <li>There can be trans record both <code>Y</code> and <code>N</code> for an <code>AccountID</code></li>
  </ol>
  
  <p>I only want to return <code>AccountIDs</code> which have transaction for statuses other than what's specified, hence why I used not in, as I do not want the 2 statuses.</p>
  
  <p>I would like to find only unique values for the <code>AccountID</code> column.</p>
  
  <p>If anyone could help refine the query below, it would be much appreciated.</p>
  
  <pre><code>SELECT AFS_Account.AddressClientID
      ,afs_transunit.AccountID
      ,SUM(afs_transunit.Units)
  FROM AFS_TransUnit
      ,AFS_Account
  WHERE afs_transunit.AccountID IN (
          -- Gets accounts which only have non post statuses
          SELECT DISTINCT accountid
          FROM afs_trans
          WHERE accountid NOT IN (
                  SELECT accountid
                  FROM afs_trans
                  WHERE STATUS IN (
                          'POSTPEND'
                          ,'POSTWAIT'
                          )
                  )
              -- This gets the unique accountIDs which only have transactions with Y status,
              -- and removes any which have both Y and N.
              AND AccountID IN (
                  SELECT DISTINCT accountid
                  FROM afs_trans
                  WHERE IsAllocated = 'Y'
                      AND accountid NOT IN (
                          SELECT DISTINCT AccountID
                          FROM afs_trans
                          WHERE IsAllocated = 'N'
                          )
                  )
          )
      AND AFS_TransUnit.AccountID = AFS_Account.AccountID
  GROUP BY afs_transunit.AccountID
      ,AFS_Account.AddressClientID
  HAVING SUM(afs_transunit.Units) &gt; 100
  </code></pre>
  
  <p>Thanks.</p>