根据客户ID计算电子邮件类型 [英] Count email type per customer id

问题描述

我有一张表格可供人们用来发送电子邮件给我们的客户。用户可以选择两个消息（消息1或消息2）。在幕后，每次他们点击SEND按钮时，它都会登录到RECORDS表中（只要它通过错误处理程序）。

让我们假装RECORDS表有2列：

  CUST_ID EMAIL_NUM 
 0000 1 
 0000 2 
 0000 1 
 0000 1 
 0001 2 
 0002 1 
 0002 1 
 0003 2 
 0003 2 
 0003 2 
  

我需要一个查询来计算每个CUST_ID的个数和两个数。所以结果集应该看起来像这样：

  CUST_ID EMAIL_1_COUNT EMAIL_2_COUNT 
 0000 3 1 
 0001 0 1 
 0002 2 0 
 0003 0 3 
  

我用count， group bys，havings，while，union，nested选择，但正如我所说的，我可能会过度复杂化一些相对容易的事情。 解决方案

> EMAIL_1_COUNT，
sum（iif（EMAIL_NUM = 1，1，0））选择
CUST_ID， 2，1，0））作为EMAIL_2_COUNT
from
RECORDS
group by
CUST_ID





I have a form that people can use to send emails to our clients. The user has an option to select between two canned messages (Message "1" or Message "2"). Behind the scenes, every time they hit the "SEND" button it logs into a "RECORDS" table (so long as it makes it through the error handlers).

Let's pretend the RECORDS table has 2 columns:

CUST_ID  EMAIL_NUM
0000         1
0000         2
0000         1
0000         1
0001         2
0002         1
0002         1
0003         2
0003         2
0003         2

I need a query that counts the ones and twos for each CUST_ID. So the result set should look something like this:

CUST_ID  EMAIL_1_COUNT  EMAIL_2_COUNT
0000          3              1
0001          0              1
0002          2              0
0003          0              3

I've used count, group bys, havings, while, union, nested selects, but like I said, I'm probably over complicating something that is relatively easy.

解决方案

select
  CUST_ID,
  sum(iif(EMAIL_NUM = 1, 1, 0)) as EMAIL_1_COUNT,
  sum(iif(EMAIL_NUM = 2, 1, 0)) as EMAIL_2_COUNT
from
  RECORDS
group by
  CUST_ID

这篇关于根据客户ID计算电子邮件类型的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！