CODEWITHSUNDEEP
sqlsql-servert-sqlpivot-table
FBeveridge
FBeveridge

Reputation: 121

Pivoting to calculate average with 1's and 0's

I need help building a pivot query from a data set that looks like this:

enter image description here

1 indicates the employee spoke with someone or the location and 0 indicates they haven't spoke with someone

I want to return a calculation the % of contacts spoken to and the % of locations spoken to by employee and then manager so the table would look like this:

enter image description here

Any ideas on how to pivot this so it calculates the percentage for each employee's assigned contacts and then the percentage for each manager's employee's assigned contacts?

Upvotes: 0

Views: 140

Answers (1)

APH
APH

Reputation: 4154

You don't need a pivot if you only have two categories. Try case statements:

Select employee as [Name]
  ,count(case when ContactType = 'Individual' and SpokeTo = 1 then LocationName end) * 100.0/ NULLIF(count(case when ContactType = 'Individual' then LocationName end), 0) as IndividualContacts
  ,count(case when ContactType = 'Location' and SpokeTo = 1 then LocationName end) * 100.0/ NULLIF(count(case when ContactType = 'Location' then LocationName end), 0) as LocationContacts
from MyTable
group by Employee

Note the *100.0 is important to avoid integer division (or you can cast explicitly to decimal). The NULLIF is optional unless you have some employees that were not assigned any contacts of one type or another - then you must include it to avoid division by 0 errors.

Upvotes: 1

Related Questions