SQL Server AVG()

SQL Server AVG() Function

The SQL Server AVG() function calculates the average value of a numeric column. This function is useful for determining the mean value of a set of numbers.

Syntax

SELECT AVG(column_name)
FROM table_name;

The AVG() function takes a single argument:

  • column_name: The name of the column for which to calculate the average.

Example SQL Server AVG() Function Queries

Let's look at some examples of SQL Server AVG() function queries:

1. Basic AVG() Example

SELECT AVG(salary) AS average_salary
FROM employees;

This query calculates the average value of the salary column in the employees table. The result will be:

average_salary
--------------
55000

2. AVG() with a Condition

SELECT AVG(salary) AS average_salary
FROM employees
WHERE department = 'Sales';

This query calculates the average value of the salary column for employees in the 'Sales' department. The result will be:

average_salary
--------------
60000

3. AVG() with a Group By Clause

SELECT department, AVG(salary) AS average_salary
FROM employees
GROUP BY department;

This query calculates the average value of the salary column for each department. The result will show the department and the corresponding average_salary.

4. AVG() with a Variable

DECLARE @department VARCHAR(50);
SET @department = 'Engineering';
SELECT AVG(salary) AS average_salary
FROM employees
WHERE department = @department;

This query uses a variable to specify the department and then calculates the average salary for that department. The result will be:

average_salary
--------------
70000

Full Example

Let's go through a complete example that includes creating a table, inserting data, and using the AVG() function.

Step 1: Creating a Table

This step involves creating a new table named employees to store some sample data.

CREATE TABLE employees (
    id INT PRIMARY KEY,
    name VARCHAR(50),
    department VARCHAR(50),
    salary DECIMAL(10, 2)
);

In this example, we create a table named employees with columns for id, name, department, and salary.

Step 2: Inserting Data into the Table

This step involves inserting some sample data into the employees table.

INSERT INTO employees (id, name, department, salary) VALUES (1, 'John Doe', 'Engineering', 75000);
INSERT INTO employees (id, name, department, salary) VALUES (2, 'Jane Smith', 'Sales', 60000);
INSERT INTO employees (id, name, department, salary) VALUES (3, 'Jim Brown', 'Engineering', 65000);
INSERT INTO employees (id, name, department, salary) VALUES (4, 'Jake White', 'Sales', 50000);
INSERT INTO employees (id, name, department, salary) VALUES (5, 'Jill Green', 'HR', 55000);

Here, we insert data into the employees table.

Step 3: Using the AVG() Function

This step involves using the AVG() function to calculate the average salary of the salary column.

SELECT AVG(salary) AS average_salary
FROM employees;

This query retrieves the average value of the salary column for all rows in the employees table. The result will be:

average_salary
--------------
61000

Conclusion

The SQL Server AVG() function is a powerful tool for calculating the average value of a numeric column. Understanding how to use the AVG() function and its syntax is essential for effective data analysis and processing in SQL Server.