Teradata: Return the count of different combinations of columns

Question

I have a dataset that looks like this:

SocialSecurityNumber | ProgramEnrollmentStatus | Contact_Preference | 
---------------------------------------------------------------------
920421                   Enrolled                Daytime
870725                   Not Enrolled            Night
630126                   Undecided               Night
630126                   Undecided               Night
920421                   Enrolled                Daytime
910510                   Undecided               Anytime
921115                   Enrolled                Night
921115                   Enrolled                Night
910510                   Undecided               Anytime
950202                   Enrolled                Daytime

Using this data, I would like to write a code that returns such data:

Contact_Preference | ProgramEnrollmentStatus | Count_Unique_SSN
Night                Enrolled                       1
Night                Undecided                      1
Night                Not Enrolled                   1
Anytime              Enrolled                       0
Anytime              Undecided                      1
Anytime              Not Enrolled                   0
Daytime              Enrolled                       2
Daytime              Undecided                      0
Daytime              Not Enrolled                   0

Answer 1

This is tricky because you have to generate the rows that have zero values. Use a cross join for that. The rest is left join and group by:

select cp.contact_preference, pes.ProgramEnrollmentStatus,
       count(distinct SocialSecurityNumber)
from (select distinct contact_preference from t) cp cross join
     (select distinct ProgramEnrollmentStatus from t) pes left join
     t
     on t.contact_preference = cp.contact_preference and
        t.ProgramEnrollmentStatus = pes.ProgramEnrollmentStatus
group by cp.contact_preference, pes.ProgramEnrollmentStatus