SQL：如果列y = z，则计算列x

I'm trying to count customers that are contactable via different channels. The following code will give me a single count of only the customers contactable via SMS.

我正在尝试计算可通过不同渠道联系的客户。以下代码将仅给出一个可通过SMS联系的客户的统计数据。

with grouping as (
select  distinct                        
case when sms_correct_flag = 'Y' then 'Y' else 'N' end as smsable,                      
case when email_correct_flag = 'Y' then 'Y' else 'N' end as emailable,                  
case when address_correct_flag = 'Y' then 'Y' else 'N' end as dmable,           
contact_key                     
from raw.contacts                   
)

select count(distinct contact_key)
from grouping 
where smsable = 'Y';

I'd like to end up with a table with 'Channel' as a column, 'Email', 'SMS', 'DM' as rows, and their respective customer counts filled.

我想最终得到一个表格，其中“频道”为专栏，“电子邮件”，“短信”，“DM”为行，各自的客户数量已填满。

This may be a count(case when....) but can't figure out how to make that work when we're checking the case for a column that is not the same as the column we are counting.

这可能是一个计数（当......的情况），但是当我们检查一个与我们正在计数的列不同的列的情况时，无法弄清楚如何进行这项工作。

Any help is appreciated!

任何帮助表示赞赏！

2 个解决方案

#1

Since you have only three channels, you can use UNION ALL to produce the results that you need:

由于您只有三个通道，因此可以使用UNION ALL生成所需的结果：

with grouping as (
    select
        MAX(case when sms_correct_flag = 'Y' then 1 else 0 end) as smsable
    ,   MAX(case when email_correct_flag = 'Y' then 1 else 0 end) as emailable
    ,   MAX(case when address_correct_flag = 'Y' then 1 else 0 end) as dmable
    ,   contact_key
    from raw.contacts
    group by contact_key
)
select 'sms' as channel, SUM(smsable) as cust_count from grouping
union all
select 'email' as channel, SUM(emailable) as cust_count from grouping
union all
select 'dm' as channel, SUM(dmable) as cust_count from grouping

Note: I do not know if Amazon's redshift has built-in pivot facilities. If it does, you may have a better approach than this poor man's pivot implementation.

注意：我不知道亚马逊的redshift是否具有内置的枢轴设施。如果是这样，你可能会有比这个穷人的支点实施更好的方法。

#2

Is this what you are looking for?

这是你想要的？

select channel,                        
       sum(case when sms_correct_flag = 'Y' then 1 else 0 end) as smsable,                      
       sum(case when email_correct_flag = 'Y' then 1 else 0 end) as emailable,                  
       sum(case when address_correct_flag = 'Y' then 1 else 0 end) as dmable          
from raw.contacts  
group by channel;

#1