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

Narendra Kumar

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

• Related Questions & Answers
• Difference between MySQL and SQL Server
• Difference between Oracle and SQL Server
• Difference between ETL and ELT in SQL Server
• Database Wars: MSSQL Server, Oracle PL/SQL and MySQL
• Difference between Star schema and Snowflake schema in SQL Server
• Finding Mean, Median, Mode in Python without Libraries
• Difference between clustered index and non-clustered index in SQL server
• MySQL LIMIT clause equivalent for SQL SERVER?
• Equivalent of SQL Server IDENTITY Column in MySQL?
• What is the use of ALLOW_INVALID_DATES SQL mode?
• What is the use of IGNORE_SPACE SQL mode?
• Are mean mode of a dataset equal in JavaScript
• Generate table DDL via a query on MySQL and SQL Server?
• Data Replication from SAP PO to SQL Server
• The equivalent of SQL Server function SCOPE_IDENTITY() in MySQL?