I have a table in SQL Server called SurveyData found here
That looks like this:
-- Create the table
CREATE TABLE SurveyData
(
country VARCHAR(50),
year INT,
val1 INT,
val2 INT,
val3 INT
);
-- Insert 10 rows of data
INSERT INTO SurveyData (country, year, val1, val2, val3)
VALUES ('USA', 2020, 4, 4, 5),
('Canada', 2021, 2, 4, 3),
('Germany', 2022, 5, 5, 4),
('France', 2020, 3, 4, 2),
('UK', 2021, 4, 2, 3),
('Australia', 2022, 3, 3, 4),
('Italy', 2020, 5, 5, 5),
('Spain', 2021, 1, 2, 3),
('Mexico', 2022, 4, 4, 4),
('Brazil', 2020, 2, 3, 1);
-- Add the mode column to the table
SELECT * FROM SurveyData
I want to create a table function that will take two parameters declared country =USA and year =2021 and the output will be the filtered table df by country USA and year 2021 and the third column will be the statistical mode (most frequent) of columns val1, val2 and val3 but row wise. How can I do it in SQL Server?
See example
-- parameters
declare @Country varchar(50)= 'USA', @Year int= 2020;
select *
from df
cross apply( select top 1 val,count(*) cnt from ( values (val1),(val2),(val3))t(val)
group by val
order by count(*) desc
)a
where country=@country and year=@year
country | year | val1 | val2 | val3 | val | cnt |
---|---|---|---|---|---|---|
USA | 2020 | 4 | 4 | 5 | 4 | 2 |