Mean and Mode in SQL Server
Problem statement
Mean is the average of the given data set calculated by dividing the total sum by the number of values in the data set.
Mode of a data set is the value that appears most frequently in a series of data
If our dataset is {1, 2, 3, 4} then mean value is − (1 + 2 + 3 + 4) / 4 = 2.5
If our dataset is {1, 2, 3, 4, 1, 1, 1, 1} then mode value is − 1 as it appears 5 times.
Example
CREATE TABLE NUMBERS (
value INT
)
- Insert data into the table −
INSERT INTO NUMBERS VALUES (1);
INSERT INTO NUMBERS VALUES (2);
INSERT INTO NUMBERS VALUES (3);
INSERT INTO NUMBERS VALUES (4);
- Find Mean using below query −
SELECT AVG(val) FROM NUMBERS;
- Insert few-mode rows with duplicate values −
INSERT INTO NUMBERS VALUES (1);
INSERT INTO NUMBERS VALUES (1);
INSERT INTO NUMBERS VALUES (1);
INSERT INTO NUMBERS VALUES (1);
- Find Mode using below query −
SELECT TOP 1 val
FROM NUMBERS
GROUP BY val
ORDER BY COUNT(*) DESC
Published on 10-Feb-2020 14:37:55
