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.

CREATE TABLE NUMBERS (
   value INT
)

INSERT INTO NUMBERS VALUES (1);
INSERT INTO NUMBERS VALUES (2);
INSERT INTO NUMBERS VALUES (3);
INSERT INTO NUMBERS VALUES (4);

SELECT AVG(val) FROM NUMBERS;

INSERT INTO NUMBERS VALUES (1);
INSERT INTO NUMBERS VALUES (1);
INSERT INTO NUMBERS VALUES (1);
INSERT INTO NUMBERS VALUES (1);

SELECT TOP 1 val
FROM NUMBERS
GROUP BY val
ORDER BY COUNT(*) DESC

Published on 10-Feb-2020 10:07:55