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

• First, create a table −

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