title: SQL Join same table on different condition and get count
body: <p>I have a table with insurance claims. It has ID, Condition that the patient has, ClaimID, EmergencyDepartmentFlag(1 - ED; 0 - NonED. I need to get the results which gives me the count of the claims per condition and count of the ED visit per condition</p>
  
  <pre><code>+----------+-----------+----------+--------+
  | MemberID | Condition | ClaimID  | EDFlag |
  +----------+-----------+----------+--------+
  | A123     | COPD      | 34124434 |      1 |
  | A526     | COPD      | 34580304 |      0 |
  | A693     | COPD      | 23723642 |      1 |
  | A645     | DM        | 46534633 |      1 |
  +----------+-----------+----------+--------+
  </code></pre>
  
  <p><strong>Expected output</strong></p>
  
  <pre><code>+-----------+-------------+----------+
  | Condition | TotalClaims | EDClaims |
  +-----------+-------------+----------+
  | COPD      |           3 |        2 |
  | DM        |           1 |        1 |
  +-----------+-------------+----------+
  </code></pre>
  
  <p>Query</p>
  
  <pre><code>SELECT condition, 
         Count(a1.claimid) AS TotalClaims, 
         Count(a2.claimid) AS EDClaims 
  FROM   (SELECT memberid, 
                 condition, 
                 claimid 
          FROM   mytable) a1 
         INNER JOIN (SELECT memberid, 
                            condition, 
                            claimid 
                     FROM   mytable 
                     WHERE  edflag = 1) a2 
                 ON a1.memberid = A2.memberid 
  GROUP  BY condition 
  </code></pre>
  
  <p>This query doesn't return the correct result.</p>