Sum unique values group by id

I have table with next columns subscriber_id, weblogin,atm/debit_card, drivers_licence, other_cards. I need to sum all columns unique values group by subscriber_id.

• so final I should have subscriber=123 weblogin = 1,atm/debit_card = 2, other_card = 1

I tried next below:

WITH cte AS(
SELECT  DISTINCT rwd.Register_Weblogin_DWM_ID AS web_logins,
   rcd.Register_Creditcard_DWM_ID AS atm_debit_credit_cards,
   Register_Driverslicense_DWM_ID AS drivers_license,
   rgd.Register_Generic_DWM_ID AS Other_cards,
   spm.customer_id AS customer_id,
   ph.member_number AS subscriber_id
FROM OpModel.[ut_Subscription] s
JOIN OpModel.[ut_Subscription_Profile] sp
    ON sp.Subscription_ID = s.Subscription_ID
JOIN OpModel.[ut_Subscription] sc ----------
    ON sc.Subscription_ID = s.Subscription_ID
LEFT JOIN OpModel.[ut_Register_Driverslicense_DWM] rdd
    ON rdd.Profile_ID = sp.Profile_ID
LEFT JOIN OpModel.[ut_Subscriber_Profile_Mapper] spm
    ON spm.Profile_ID = rdd.Profile_ID
JOIN OpModel.[ut_Register_Weblogin_DWM] rwd --????
    ON rwd.Profile_ID = rdd.Profile_ID
LEFT JOIN OpModel.[ut_Register_Creditcard_DWM] rcd
    ON rcd.Profile_ID = rdd.Profile_ID
LEFT JOIN OpModel.[ut_Register_Generic_DWM] rgd
    ON rgd.Profile_ID = rdd.Profile_ID
JOIN OpModel.ut_Profile ph
    ON ph.Profile_ID = rdd.Profile_ID)
,cte1 AS(
    SELECT cte.subscriber_id,
           SUM(DISTINCT CASE WHEN cte.web_logins IS NOT NULL THEN 1 ELSE 0 END) AS web_logins,
           SUM(DISTINCT CASE WHEN cte.atm_debit_credit_cards IS NOT NULL THEN 1 ELSE 0 end) AS 
atm_debit_credit_cards,
           sum(DISTINCT CASE WHEN cte.drivers_license IS NOT NULL THEN 1 ELSE 0 END) AS 
drivers_license,
           sum(DISTINCT CASE WHEN cte.Other_cards IS NOT NULL THEN 1 ELSE 0 END) AS 
Other_cards
    FROM cte
    GROUP BY cte.subscriber_id
    WITH rollup
     )

    SELECT *
    FROM cte1 

>Solution :

You can simply use count(DISTINCT ...).

SELECT subscriber_id,
       count(DISTINCT weblogin) AS weblogin,
       count(DISTINCT atm_debit_card) AS atm_debit_card,
       count(DISTINCT other_card) AS other_card
       FROM elbat
       GROUP BY subscriber_id;