添加列出现次数

你把事情复杂化了,而不是逐一计算 SatisfactionLevel 作为一个单独的组,然后必须将其转回,只需使用条件聚合。

-- Parameters
DECLARE @Year INT = 2020; --, @Country varchar(50)= 'Brazil';

WITH ModeData AS (
    SELECT country, 
           a.Mode
    FROM df
    CROSS APPLY (
        SELECT TOP 1 Mode, COUNT(*) AS cnt
        FROM (VALUES (val1), (val2), (val3)) AS t(Mode)
        GROUP BY Mode
        ORDER BY COUNT(*) DESC
    ) a
    where year = @year --and  country=@country 
)

SELECT
  country, 
  COUNT(CASE WHEN Mode = 1 THEN 1 END) * 1.0 / COUNT(*) AS [Very Dissatisfied], 
  COUNT(CASE WHEN Mode = 2 THEN 1 END) * 1.0 / COUNT(*) AS [Dissatisfied], 
  COUNT(CASE WHEN Mode = 3 THEN 1 END) * 1.0 / COUNT(*) AS [Neutral],
  COUNT(CASE WHEN Mode = 4 THEN 1 END) * 1.0 / COUNT(*) AS [Satisfied], 
  COUNT(CASE WHEN Mode = 5 THEN 1 END) * 1.0 / COUNT(*) AS [Very Satisfied],
  COUNT(*) AS Count
FROM ModeData
GROUP BY
  country
ORDER BY
  country;

db<>fiddle

添加额外的CTE来解决国家计数,然后将其纳入最终结果。

样品表

-- Create the table
CREATE TABLE df (
    country VARCHAR(50),
    year INT,
    val1 INT,
    val2 INT,
    val3 INT
);

-- Insert 10 rows of data
INSERT INTO df (country, year, val1, val2, val3) VALUES
('USA', 2020, 4, 4, 5),
  ('USA', 2020, 4, 4, 5),
  ('USA', 2020, 5, 5, 5),
  ('USA', 2020, 5, 5, 5),
  ('USA', 2020, 1, 1, 5),
  ('USA', 2020, 3, 3, 5),
  ('USA', 2020, 4, 2, 5),
  ('USA', 2020, 1, 1, 5),
  ('USA', 2020, 2, 2, 5),
  ('Canada', 2020, 1, 1, 3),
  ('Canada', 2020, 2, 2, 3),
  ('Canada', 2020, 4, 4, 3),
  ('Canada', 2020, 3, 4, 3),
  ('Canada', 2020, 3, 4, 3),
  ('Canada', 2020, 3, 4, 3),
  ('Canada', 2020, 5, 4, 3),
  ('Canada', 2020, 5, 4, 5),
  ('Canada', 2020, 5, 4, 5),
('Germany', 2022, 5, 5, 4),
('France', 2020, 1,1, 2),
  ('France', 2020, 1,1, 2),
  ('France', 2020, 3, 2, 2),
  ('France', 2020, 3, 4, 2),
  ('France', 2020, 3, 5, 5),
  ('France', 2020, 3, 4, 4),
  ('France', 2020, 3, 4, 4),
  ('France', 2020, 3, 4, 3),
('UK', 2021, 4, 2, 3),
('Australia', 2022, 3, 3, 4),
('Italy', 2020, 5, 5, 5),
  ('Italy', 2020, 5, 5, 5),
  ('Italy', 2020, 5, 1,1),
   ('Italy', 2020, 4, 4,1),
   ('Italy', 2020, 2, 1,2),
  ('Italy', 2020, 3, 5, 3),
('Spain', 2021, 1, 2, 3),
('Mexico', 2022, 4, 4, 4),
    ('Brazil', 2020, 4, 1, 1),
  ('Brazil', 2020, 4, 1, 1),
  ('Brazil', 2020, 4, 3, 4),
  ('Brazil', 2020, 5, 3, 5),
    ('Brazil', 2020, 5, 3, 5),
  ('Brazil', 2020, 3, 3, 1),
('Brazil', 2020, 2, 3, 1);
-- Add the mode column to the table
select * from df;

查询

-- Parameters
DECLARE @Year INT = 2020; --, @Country varchar(50)= 'Brazil';

WITH ModeData AS (
    SELECT country, 
           a.Mode
    FROM df
    CROSS APPLY (
        SELECT TOP 1 Mode, COUNT(*) AS cnt
        FROM (VALUES (val1), (val2), (val3)) AS t(Mode)
        GROUP BY Mode
        ORDER BY COUNT(*) DESC
    ) a
  where year=@year --and  country=@country 
)

-- Calculate proportions and map modes to labels
, Proportions AS (
    SELECT country, 
           CASE 
               WHEN Mode = 1 THEN 'Very Dissatisfied'
               WHEN Mode = 2 THEN 'Dissatisfied'
               WHEN Mode = 3 THEN 'Neutral'
               WHEN Mode = 4 THEN 'Satisfied'
               WHEN Mode = 5 THEN 'Very Satisfied'
           END AS SatisfactionLevel,
           COUNT(*) * 1.0 / SUM(COUNT(*)) OVER (PARTITION BY country) AS Proportion
    FROM ModeData
    GROUP BY country, Mode
)

, Pivoted AS (
-- Pivot the results to get each satisfaction level as a column
SELECT Country, 
       [Very Dissatisfied], 
       [Dissatisfied], 
       [Neutral], 
       [Satisfied], 
       [Very Satisfied]
FROM Proportions
PIVOT (
    MAX(Proportion)
    FOR SatisfactionLevel IN ([Very Dissatisfied], [Dissatisfied], [Neutral], [Satisfied], [Very Satisfied])
) AS p
),

CountryCounts AS (
-- Count of countries from original df table
SELECT Country,
       COUNT(Country) AS Total
FROM df
GROUP BY Country)

SELECT Pivoted.Country,
    [Very Dissatisfied],
    Dissatisfied,
    Neutral,
    Satisfied,
    [Very Satisfied],
    Total
FROM Pivoted
INNER JOIN CountryCounts
ON Pivoted.Country = CountryCounts.Country
ORDER BY Pivoted.Country;