I'm having trouble obtaining data and categorizing it within a select without filtering it through the group by clause.

I have a table populated with the results of a customer satisfaction poll, which includes the office at which the client was served, the type of service he was provided, and the score given by the client.

I need to know the NPS scores and group them by business and office. (ID column not included because I can barely fit it, but it's there)

The thing is, I need to count the amount of scores between 0 and 6 (detractors), between 7 and 8 (passives), and 9 and 10 (promoters). So it becomes something like this:

To calculate the nps_score = (qty_promoters/qty_answers) - (qty_detractors /qty_answers)

I thought of doing a count of scores to get the total amount of answers from every segment, but I don't know how to then spread the data according to my needs.

select 
    business,
    office,
    count(score) as total
from dbo.poll_results
group by business,
    office

I thought about making a temporary table in which I could store the id's of each answer and classify the results into the 3 categories.

select id, 
    score, 
    "type" = 
    case
        when score <= 6  then 'detractor'
        when score > 6 and score < 9 then 'passive'
        when score >= 9 then 'promoter'
        end
into #tmp_NPS_scores
from dbo.poll_results

And then joining the two, but I'm very confused about how this is going to turn out and so far I've been hitting walls trying to structure the query.

I've been investigating about PIVOT(), but I still don't quite get it I believe, tried playing with it but I only got errors in return. Should I pursue this as a solution?

I hope I could make the problem as understandable as possible, I'm working with over ten columns and I believe this is enough for anyone to comprehend.

Thank you in advance.

Oh and just in case anyone wonders, I know this can be done with BI tools, in fact I need to make a dashboard out of this in Tableau, but I was given the task to relieve some of the load off Tableau by making the NPS calculation inside the table.

CodePudding user response：

You can try and use CTE - common table expressions, temporary results sets to separate the count query into 3 needed groups and then build all the results together.

This is a readable and straightforward solution.

;WITH detractors AS 
 ( 
   SELECT service, office, COUNT(score) as count
   FROM poll
   WHERE score <=6
   GROUP by service, office
),
passives AS 
 ( 
   SELECT service, office, COUNT(score) as count
   FROM poll
   WHERE score > 6 AND score <= 9
   GROUP by service, office
),
promoters AS 
 ( 
   SELECT service, office, COUNT(score) as count
   FROM poll
   WHERE score > 9
   GROUP by service, office
),
totals AS 
 ( 
   SELECT service, office, COUNT(score) as count
   FROM poll
   GROUP by service, office
),
SELECT p.service, p.office, pro.count as promoters, det.count as detractors, pas.count as passives, ((pro.count/tot.count) - (det.count /tot.count)) as nps_score
FROM poll p
LEFT JOIN promoters pro ON p.office = pro.office AND p.service = pro.service
LEFT JOIN passives pass ON p.office = pass.office AND p.service = pass.service
LEFT JOIN detractors det ON p.office = det.office AND p.service = det.service
LEFT JOIN totals tot ON p.office = tot.office AND p.service = tot.service

CodePudding user response：

with breakout as (
    select service, office,
        count(case when score between 0 and 6  then 1 end) as qty_detractors,
        count(case when score between 7 and 8  then 1 end) as qty_passives,
        count(case when score between 9 and 10 then 1 end) as qty_promoters,
        count(*) as qty_answers
    from dbo.poll_results
    group by service, office
)
select *, (qty_promoters - qty_detractors) / qty_answers as NPS_score
from breakout

This is going to be a lot more efficient than executing separate queries and combining them all via joins and then having to deal with nulls values where there were zeroes...