SQL query to count number of accounts based on criteria A only, B only and both A&B

Basically, I have a table that tells about type of communication to accounts(either by letter or email) like :

ACOUNT_ID	TYPE_OF_COMM
1	Letter
1	Letter
3	EMail
2	Letter
4	EMail
4	Letter

and so on.. A query needs to be written that counts :

1. the number of accounts that received letter only,
2. the number of accounts that received mails only,
3. the number of accounts that received both letters and mails

So the output of above should be :

Type_of_comms	Count of Accounts
Letters only	2
EMail only	1
both letters and mails	1

Thanks in advance!

Answer

I’m not familiar with Athena, but as long as it supports CTEs / subqueries, this should work:

--if CTE not supported, just change to a subquery
with comm_type as
(
    select distinct t1.ACCOUNT_ID
        , case
            when t1.TYPE_OF_COMM like 'Email' and t2.TYPE_OF_COMM is null then 'Email Only'
            when t1.TYPE_OF_COMM like 'Letter' and t2.TYPE_OF_COMM is null then 'Letter Only'
            when t1.TYPE_OF_COMM is not null and t2.TYPE_OF_COMM is not null then 'Both Letters and Mails'
            else 'Unkown'
          end TYPE_OF_COMMS
    from tbl t1
    left join tbl t2
        on t1.ACCOUNT_ID = t2.ACCOUNT_ID
        and t1.TYPE_OF_COMM <> t2.TYPE_OF_COMM
)
select TYPE_OF_COMMS 
    , count(distinct ACCOUNT_ID)
from comm_type
group by TYPE_OF_COMMS