T-SQL script produces wrong calculation-CodePudding

The script below has an issue when it encounter division or averages etc. I would like to produce a result in the screen shot below but there is an "N/A" value in the column which messes up the counts, sums, averages etc. If I exclude the count of N/A then it returns zero 0 which messes up the averages. It incorrectly calculates as there should be 17 rows and and there are 7 5's in the result set as you can see from the Calculations arrow so the division of 7/17 which should be equal to 0.411764 but it's 0.388889. Any ideas?

   Create Table Test
(
    Q4 NVARCHAR(255),
    Q5 NVARCHAR(255)
);
 
INSERT  INTO Test
(
    Q4,
    Q5
)
VALUES  ('Strongly Agree', 'Agree'),
        ('Neutral', 'Disagree'),
        ('Neutral', 'Agree'),
        ('Strongly Agree', 'Disagree'),
        ('Agree', 'Strongly Disagree'),
        ('Strongly Disagree', 'Agree'),
        ('Strongly Agree', 'Disagree'),
          ('Agree', 'N/A'), -- Calculation Issue here because of N/A
        ('Agree', 'Strongly Disagree');
 
 
WITH cte1 AS (
SELECT
ROW_NUMBER() OVER (ORDER BY (SELECT 1)) AS rn,
CASE WHEN [Q4] = 'Strongly Agree' THEN 5
WHEN [Q4] = 'Agree' THEN 4
WHEN [Q4] = 'Neutral' THEN 3
WHEN [Q4] = 'Disagree' THEN 2
WHEN [Q4] = 'Strongly Disagree' THEN 1
--WHEN [Q4] = 'N/A' THEN NULL
--when nullif([Q4], 'N/A') IS NOT NULL THEN 1 ELSE 0
END AS TestQ4,
CASE WHEN [Q5] = 'Strongly Agree' THEN 5
WHEN [Q5] = 'Agree' THEN 4
WHEN [Q5] = 'Neutral' THEN 3
WHEN [Q5] = 'Disagree' THEN 2
WHEN [Q5] = 'Strongly Disagree' THEN 1
END AS TestQ5
 
FROM  Test
--where [Q4] <> 'N/A'
),
RolledUp AS (
    SELECT
      rn,    
         TestQ4, -- Question 4
         TestQ5, -- Question 5
 
       grouping = GROUPING(TestQ4),
      count = COUNT(*),
      count2 = COUNT(TestQ4),
   
        -- Question 4 as TestQ4
         sum4 = SUM(TestQ4),
      avg4 = AVG(TestQ4 * 1.0),
      one4 = COUNT(CASE WHEN TestQ4 = 1 THEN 1 END),
     
       onePct4 = COUNT(CASE WHEN TestQ4 = 1 THEN 1 END) * 1.0 / COUNT(*),
       CountFourOf_Two = COUNT(CASE WHEN TestQ4 = 2 THEN 1 END),
       onePct4CountFourOf_Two = COUNT(CASE WHEN TestQ4 = 2 THEN 1 END) * 1.0 / COUNT(*),
       CountFourOfThree = COUNT(CASE WHEN TestQ4 = 3 THEN 1 END),
       onePct4CountFourOfThree = COUNT(CASE WHEN TestQ4 = 3 THEN 1 END) * 1.0 / COUNT(*),
         CountFourOfFour = COUNT(CASE WHEN TestQ4 = 4 THEN 1 END),
       onePct4CountFourOfFour = COUNT(CASE WHEN TestQ4 = 4 THEN 1 END) * 1.0 / COUNT(*),
     
       CountFourOfFive = COUNT(CASE WHEN TestQ4 = 5 THEN 1 END),
       onePct4CountFourOfFive = COUNT(CASE WHEN TestQ4 = 5 THEN 1 END) * 1.0 / COUNT(*),


 -- Question 5 as TestQ5
      sum5 = SUM(TestQ5),
      avg5 = AVG(TestQ5 * 1.0),
      one5 = COUNT(CASE WHEN TestQ5 = 1 THEN 1 END),
      
       onePct5 = COUNT(CASE WHEN TestQ5 = 1 THEN 1 END) * 1.0 / COUNT(*),
       CountFiveOf_Two = COUNT(CASE WHEN TestQ5 = 2 THEN 1 END),
       onePct5CountFiveOf_Two = COUNT(CASE WHEN TestQ5 = 2 THEN 1 END) * 1.0 / COUNT(*),
 
       CountFiveOfThree = COUNT(CASE WHEN TestQ5 = 3 THEN 1 END),
       onePct5CountFiveOfThree = COUNT(CASE WHEN TestQ5 = 3 THEN 1 END) * 1.0 / COUNT(*),
 
         CountFiveOfFour = COUNT(CASE WHEN TestQ5 = 4 THEN 1 END),
       onePct5CountFiveOfFour = COUNT(CASE WHEN TestQ5 = 4 THEN 1 END) * 1.0 / COUNT(*),
      
       CountFiveOfFive = COUNT(CASE WHEN TestQ5 = 5 THEN 1 END),
       onePct5CountFiveOfFive = COUNT(CASE WHEN TestQ4 = 5 THEN 1 END) * 1.0 / COUNT(*)
     
 
    FROM cte1
    GROUP BY GROUPING SETS(
        (rn, TestQ4, TestQ5),
        ()
    )
)
SELECT  v.TestQ4, v.TestQ5
FROM RolledUp r
CROSS APPLY (
    SELECT TestQ4,TestQ5,  0 AS ordering
    WHERE grouping = 0
    UNION ALL
    SELECT value1,value2, ordering
    FROM (VALUES
        (NULL,NULL, 1),
        (count2,COUNT,  2),
        (sum4,sum5, 3),
        (avg4,avg5, 4),
        (one4,one5, 5),
        (onePct4,onePct5, 6),
              (CountFourOf_Two,CountFiveOf_Two, 7),
              (onePct4CountFourOf_Two,onePct5CountFiveOf_Two, 8),
              (CountFourOfThree, CountFiveOfThree, 9),
              (onePct4CountFourOfThree,onePct5CountFiveOfThree, 10),
              (CountFourOfFour,CountFiveOfFour, 11),
              (onePct4CountFourOfFour,onePct5CountFiveOfFour, 12),
              (CountFourOfFive,CountFiveOfFive,13),
              (onePct4CountFourOfFive,onePct5CountFiveOfFive, 14)
            
    ) v(value1,value2, ordering)
    WHERE grouping = 1
) v
ORDER BY ordering, rn;

CodePudding user response：

Your onePct5CountFiveOfFive calculation is referencing the wrong question. That looks to be the cause of your current problem.

CodePudding user response：

I changed the last calculations for both questions TestQ4, and TestQ5 like this and it worked. onePct4CountFourOfFive = COUNT(CASE WHEN TestQ4 = 5 THEN 1 END) * 1.0 / NULLIF(COUNT(TestQ4),0)