title: Horizontal DISTINCT COUNT in SQL Server or a COUNT IF without counting duplicates
body: <p>I have over simplified the examples that follow, however please know that I really do need the results as I describe.</p>
  
  <p>I have a table that contains information about customers including how they were referred.
  For example <code>MyTable</code> (EDIT: My over simplification resulted in answers that would not work. They are good answers, but do not solve my problem. I have added the last piece of the puzzle for clarity):</p>
  
  <pre><code>STORE    CUS_ID REFERRED    MONEYMADE  
   1        11        RADIO         10.00
   1        15        WALKIN        20.00
   1        11        RADIO         30.00
   2        12        RADIO         40.00
   2        12        RADIO         50.00
   3        13        WALKIN        60.00
   3        14        WALKIN        70.00
  </code></pre>
  
  <p>I want to count the number of <strong>DISTINCT</strong> customers that were referred by a certain type and group them by store. I could do the following:</p>
  
  <pre><code>SELECT 
     STORE, COUNT(DISTINCT CUS_ID) AS COUNTEM, 
     REFERRED, SUM(MONEYMADE) 
  FROM MyTable 
  GROUP BY REFERRED, STORE
  </code></pre>
  
  <p>This gives the following result:</p>
  
  <pre><code>STORE    COUNTEM    REFERRED    MONEYMADE
   1        1         RADIO        40.00
   1        1         WALKIN       20.00
   2        1         RADIO        90.00
   3        2         WALKIN       130.00
  </code></pre>
  
  <p>However, I really need the results like this:</p>
  
  <pre><code>STORE    RADIO  RADIO_MONEY   WALKIN   WALKIN_MONEY
   1        1      40.00         1         20.00
   2        1      90.00         0         0.00
   3        0      0.00          2         130.00
  </code></pre>
  
  <p>I attempted a query using SUM and CASE but it counts duplicates. For example:</p>
  
  <pre><code>SELECT 
     STORE, 
     SUM (CASE REFERRED WHEN 'RADIO' THEN 1 ELSE 0 END) AS RADIO,  
     SUM (CASE REFERRED WHEN 'RADIO' THEN MONEYMADE ELSE 0 END) AS RADIO_MONEY,  
     SUM (CASE REFERRED WHEN 'WALKIN' THEN 1 ELSE 0 END) AS WALKIN
     SUM (CASE REFERRED WHEN 'WALKIN' THEN MONEYMADE ELSE 0 END) AS WALKIN_MONEY, 
  FROM MyTable
  GROUP BY STORE
  </code></pre>
  
  <p>Returns this incorrect result:</p>
  
  <pre><code>STORE    RADIO  RADIO_MONEY   WALKIN   WALKIN_MONEY
   1        2      40.00         1         20.00
   2        2      90.00         0         0.00
   3        0      0.00          2         130.00
  </code></pre>
  
  <p>Is there any way to pull this off? I have searched for "count if" type of functions but the only thing I have found is the sum - case method. </p>