Horizontal DISTINCT COUNT in SQL Server or a COUNT IF without counting duplicates

I have over simplified the examples that follow, however please know that I really do need the results as I describe.

I have a table that contains information about customers including how they were referred. For example MyTable (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):

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

I want to count the number of DISTINCT customers that were referred by a certain type and group them by store. I could do the following:

SELECT 
   STORE, COUNT(DISTINCT CUS_ID) AS COUNTEM, 
   REFERRED, SUM(MONEYMADE) 
FROM MyTable 
GROUP BY REFERRED, STORE

This gives the following result:

STORE    COUNTEM    REFERRED    MONEYMADE
 1        1         RADIO        40.00
 1        1         WALKIN       20.00
 2        1         RADIO        90.00
 3        2         WALKIN       130.00

However, I really need the results like this:

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

I attempted a query using SUM and CASE but it counts duplicates. For 开发者_开发问答example:

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

Returns this incorrect result:

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

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.

---

EDIT Since you need to SUM another Field. you should do the sum before the SUM/CASE

With self-contained sample data

--TEST DATA
DECLARE @MyTable table 
(STORE int,  CUST_ID int , Referred varchar(6) ,MONEYMADE   Money )

INSERT INTO @MyTable VALUES  
(1,        11,        'RADIO' ,        10.00)
INSERT INTO @MyTable VALUES  
(1,        15,        'WALKIN',        20.00)
INSERT INTO @MyTable VALUES 
(1,        11,        'RADIO' ,       30.00)
INSERT INTO @MyTable VALUES 
(2,        12,        'RADIO' ,        40.00)
INSERT INTO @MyTable VALUES 
(2,        12,        'RADIO' ,       50.00)
INSERT INTO @MyTable VALUES 
(3,        13,        'WALKIN',        60.00)
INSERT INTO @MyTable VALUES 
(3,        14,        'WALKIN',        70.00)

--USING PIVOT

  SELECT 
    pk.STORE,
    pk.RADIO,
    pk.WALKIN,
    ISNULL(pv.RADIO,0) as RADIO_MONEY,
    ISNULL(pv.WALKIN,0)as WALKIN_MONEY

 FROM
 (SELECT  STORE,   
          REFERRED ,
          MONEYMADE   
  FROM  @MyTable) p
          PIVOT (SUM(MONEYMADE) FOR REFERRED in (WALKIN, RADIO)) as  pv
  INNER JOIN         
 ( SELECT  DISTINCT STORE,   
                    REFERRED ,
                    CUST_ID   
   FROM  @MyTable) p
            PIVOT ( COUNT (CUST_ID) FOR REFERRED in (WALKIN, RADIO)) as  pk
  ON pv.store = pk.STORE

--WITHOUT Using PIVOT

SELECT 
   STORE,
   SUM(CASE REFERRED WHEN  'RADIO' THEN 1 ELSE 0 END )AS RADIO,
   SUM(CASE REFERRED WHEN 'WALKIN' THEN 1 ELSE 0 END )AS WALKIN,
   SUM(CASE REFERRED WHEN  'RADIO' THEN MONEYMADE ELSE 0 END )AS RADIO_MONEYMADE,
   SUM(CASE REFERRED WHEN 'WALKIN' THEN MONEYMADE ELSE 0 END )AS WALKIN_MONEYMADE
FROM   
   (

SELECT  
    STORE,   
    CUST_ID, 
    REFERRED ,
    SUM(MONEYMADE )MONEYMADE
  FROM 
    @MyTable p
  GROUP BY 
  STORE,
  REFERRED,
  CUST_ID


) t
group by store

Output For both techniques

    STORE       RADIO       WALKIN      RADIO_MONEY           WALKIN_MONEY
    ----------- ----------- ----------- --------------------- ---------------------
    1           1           1           40.00                 20.00
    2           1           0           90.00                 0.00
    3           0           2           0.00                  130.00

---

Rotating SQL results is at minimum tricky. If you want to present data right from the query, you may have to sacrifice query flexibility and hardcode a good deal of information (like column names and their count).

So what I'd like to suggest is doing a bit more on the backend, where the query is actually used. If it's acceptable and you can rotate the results yourself then you can the following:

;with R(Referred) as (
  select distinct REFERRED from MyTable
)
select t.STORE, COUNT(*) as COUNT, t.REFERRED
from MyTable t
  left join R on r.REFERRED = t.REFERRED
group by t.STORE, t.REFERRED

---

Here's your script modified to comply, I think, with what you are trying to achieve:

SELECT 
   STORE, 
   COUNT(DISTINCT CASE REFERRED WHEN 'RADIO' THEN CUS_ID END) AS RADIO,  
   SUM  (CASE REFERRED WHEN 'RADIO' THEN MONEYMADE ELSE 0 END) AS RADIO_MONEY,  
   COUNT(DISTINCT CASE REFERRED WHEN 'WALKIN' THEN CUS_ID END) AS WALKIN
   SUM  (CASE REFERRED WHEN 'WALKIN' THEN MONEYMADE ELSE 0 END) AS WALKIN_MONEY, 
FROM MyTable
GROUP BY STORE