title: Select count() when field is unique
body: <p>I'm using SQL in vfp so some of my commands are different. <code>iif([condition],[if true],[if false])</code> is used instead of <code>case when</code>.</p>
  
  <p>here's my code:</p>
  
  <pre><code>SELECT
      giftsource,
      COUNT(donor) AS total_gifts, /*this is not the one that need fixed*/
      SUM(amt) AS total_amt,
      SUM(iif(unique = 1 AND language != 'F' AND renew = '0' AND type = 'IN',1,0)) as d_Count_E_New_Indiv,
      SUM(iif(unique = 1 AND language = 'F' AND renew = '0' AND type = 'IN',1,0)) as d_Count_F_New_Indiv,
      SUM(iif(unique = 1 AND language != 'F' AND renew != '0' AND type = 'IN',1,0)) as d_Count_E_Re_Indiv,
      SUM(iif(unique = 1 AND language = 'F' AND renew != '0' AND type = 'IN',1,0)) as d_Count_F_Re_Indiv,
      SUM(iif(unique = 1 AND language != 'F' AND renew = '0' AND type != 'IN',1,0)) as d_Count_E_New_Org,
      SUM(iif(unique = 1 AND language = 'F' AND renew = '0' AND type != 'IN',1,0)) as d_Count_F_New_Org,
      SUM(iif(unique = 1 AND language != 'F' AND renew != '0' AND type != 'IN',1,0)) as d_Count_E_Re_Org,
      SUM(iif(unique = 1 AND language = 'F' AND renew != '0' AND type != 'IN',1,0)) as d_Count_F_Re_Org,
  FROM (select *,
              cast( /* equivalent to a bunch of if elses*/
                  iif(  list_code="WEB","1",
                  iif(  list_code="GRO","2",
                  iif(  list_code="CHO","3",
                  iif(  list_code="TEL","4",
                  iif(  list_code="TES","5",
                  iif(  list_code="POS" AND amt &lt; 10000,"6",
                  iif( (LIKE(list_code,"4%") OR list_code = "4") AND amt &lt; 10000,"7",
                  iif( (LIKE(list_code,"4%") OR list_code = "4" OR list_code = "POS") AND amt &gt;= 10000,"8",
                  iif(  LIKE(list_code,"9%") OR list_code = "9","9",
                  "10"))))))))) as c(1))
              as giftsource
          from cGift) gift
      LEFT JOIN
              (select didnumb, language, type from dp) d
          on cast(gift.donor as i) = cast(d.didnumb as i)
      LEFT JOIN /*this does not do what i want it to*/
              (select min(gidnumb) gid, 1 as unique from cGift group by donor) uGift
          on gift.gidnumb = uGift.gid
  GROUP BY giftsource
  ORDER BY giftsource
  </code></pre>
  
  <p>What this code should do is find how many donors donated within each gift category. It shouldn't count duplicate donors within the same field (i.e. same list_code/lang/renew/etc.) but it should count a donor twice if it's another field.</p>
  
  <p>Example: donor #3 can only be counted once in d_Count_E_New_Indiv but can also be counted in d_Count_E_New_Org.</p>
  
  <p>gidnumb is the primary key in this table.</p>
  
  <p>With my second join i attached a field (named unique) to the first donor in the table. This doesn't work because it only counts the donor in one field.</p>
  
  <p>Can someone tell me what the right way to do it is? Also i have a lot more SUM(...) that aren't based on unique donors so i'd prefer to not butcher my select too much.</p>
  
  <p>EDIT: i fixed it by using the following <code>select count(distinct IIF(renew = '0' AND lang != 'F',donor,0)) FROM dpgift</code></p>