Count email type per customer id

Question

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.

Posting those attempt(s) would certainly make it easier to point out where you went wrong. :) — GolezTrol, Jan 23 '12 at 16:23

score 4 · Accepted Answer · answered Jan 23 '12 at 16:24

4

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

answered Jan 23 '12 at 16:24

GolezTrol

114,394
18
182
210

score 1 · Answer 2 · answered Jan 23 '12 at 16:56

Another option to consider is to use a pivot query with TRANSFORM

TRANSFORM NZ(Count(RECORDS.Email_NUm),0) AS CountOfEmail_NUm
SELECT RECORDS.CUST_ID
FROM RECORDS
GROUP BY RECORDS.CUST_ID
PIVOT RECORDS.Email_NUm;

This would however produce column heads of CUST_ID , 1, and 2. However if you had another table with the email types then it might be worthwhile (especially if you had a large number of email types than 2)

The SQL might look like this

TRANSFORM NZ(Count(r.Email_NUm),0) AS CountOfEmail_NUm
SELECT r.CUST_ID
FROM RECORDS r
     INNER JOIN EMAIL_TYPES et
     ON r.Email_NUm = et.Email_NUm
GROUP BY r.CUST_ID
PIVOT et.TYPE_NAME;

Producing this output

   CUST_ID | Work | Home 
   -------   ----   ----
   0000    | 3    | 1
   0001    | 0    | 1
   0002    | 2    | 0
   0003    | 0    | 3

Interesting response. As the form grows and I add more canned messages, I may have to use this response. For now the first response works fine, and should work fine for awhile until performance issues occur. — RS3, Jan 23 '12 at 18:06

Count email type per customer id

2 Answers2

Linked