In SQL, is there something similar to countif statement or a way to show grouped results as columns instead of rows?

user_16967244 0
Viewed 19

Is there a way to apply a filter to a distinct count using SQL, similar using the countif statement in Excel? Or maybe a way of using the Group By statement but list each unique group as columns instead of rows.

The database I am querying looks something like this:

Name Case # Case Type
Joey 123 Quote
Joey 124 Endorsement
Joey 125 Endorsement
Joey 126 Endorsement
Sarah 127 Quote
Sarah 128 Phone Call
Sarah 129 Endorsement

What I want to do is end up with something like this:

Name Endorsement Quote Phone Call
Joey 3 1 0
Sarah 1 1 1

My current code:

Select Case_Owner_Name__c,Case_Queue__c As Case_Queue, 
  Count(Distinct CaseNumber) As Case_Count 
from slss.case
Group By Case_Owner_Name__c,Case_Queue__c;

Is there a way to filter the Count(Distinct CaseNumber) so that it only counts the Case # if it matches a certain Case Type?

azure sql
1 Answers

You can use traditional conditional aggregation to pivot. For example:

select
  name,
  sum(case when case_type = 'Endorsement' then 1 else 0 end) as endorsement,
  sum(case when case_type = 'Quote' then 1 else 0 end) as quote,
  sum(case when case_type = 'Phone Call' then 1 else 0 end) as phone_call
from case
group by name
user_64361910
Related