Comparing Column's Value with Other Column and Based on Condition Choose Value from Third Column SQL

Counting Accounts Based on Communication Type Using SQL Joins and Subqueries