SQL AVG Function

SQL AVG Function

The SQL AVG function is used to calculate the average value of a numeric column. This function is essential for performing various aggregate operations, such as calculating average sales, salaries, or any other numeric data.

---

Syntax

SELECT AVG(column_name)
FROM table_name
WHERE condition;

The AVG function returns the average value of the specified column. The WHERE clause is optional and can be used to filter the rows before applying the AVG function.

---

Example SQL AVG Queries

Let's look at some examples of SQL AVG queries using the employees table:

1. Basic AVG Example

SELECT AVG(salary) AS avg_salary
FROM employees;

This query uses the AVG function to calculate the average salary of all employees in the employees table.

2. AVG with WHERE Clause

SELECT AVG(salary) AS avg_salary
FROM employees
WHERE department_id = 3;

This query uses the AVG function to calculate the average salary of employees in department 3.

3. AVG with GROUP BY

SELECT department_id, AVG(salary) AS avg_salary
FROM employees
GROUP BY department_id;

This query uses the AVG function to calculate the average salary for each department in the employees table.

4. AVG with JOIN

SELECT d.department_name, AVG(e.salary) AS avg_salary
FROM employees e
JOIN departments d ON e.department_id = d.department_id
GROUP BY d.department_name;

This query uses the AVG function to calculate the average salary for each department by joining the employees and departments tables.

---

Full Example

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

Step 1: Creating a Database

This step involves creating a new database named example_db.

CREATE DATABASE example_db;

In this example, we create a database named example_db.

Step 2: Creating Tables

In this step, we create two tables named employees and departments within the previously created database.

USE example_db;

CREATE TABLE departments (
    department_id INT AUTO_INCREMENT PRIMARY KEY,
    department_name VARCHAR(50) NOT NULL
);

CREATE TABLE employees (
    employee_id INT AUTO_INCREMENT PRIMARY KEY,
    first_name VARCHAR(50) NOT NULL,
    last_name VARCHAR(50) NOT NULL,
    department_id INT,
    salary DECIMAL(10, 2),
    FOREIGN KEY (department_id) REFERENCES departments(department_id)
);

Here, we define the departments table with columns for department_id and department_name. We also define the employees table with columns for employee_id, first_name, last_name, department_id, and salary, with a foreign key relationship to the departments table.

Step 3: Inserting Data into the Tables

This step involves inserting some sample data into the departments and employees tables.

INSERT INTO departments (department_name) VALUES ('HR'), ('Finance'), ('IT'), ('Marketing');

INSERT INTO employees (first_name, last_name, department_id, salary) VALUES ('John', 'Doe', 1, 50000.00);
INSERT INTO employees (first_name, last_name, department_id, salary) VALUES ('Jane', 'Smith', 2, 60000.00);
INSERT INTO employees (first_name, last_name, department_id, salary) VALUES ('Alice', 'Johnson', 3, 70000.00);
INSERT INTO employees (first_name, last_name, department_id, salary) VALUES ('Bob', 'Brown', 4, 80000.00);
INSERT INTO employees (first_name, last_name, department_id, salary) VALUES ('Charlie', 'Davis', 1, 90000.00);

Here, we insert data into the departments table and then insert data into the employees table, ensuring that each employee is assigned to a department.

Step 4: Using the AVG Function

This step involves using the AVG function to perform various aggregate operations on the employees table.

Example 1: Calculating Average Salary

SELECT AVG(salary) AS avg_salary
FROM employees;

This query calculates the average salary of all employees in the employees table.

Example 2: Calculating Average Salary for a Specific Department

SELECT AVG(salary) AS avg_salary
FROM employees
WHERE department_id = 3;

This query calculates the average salary of employees in department 3.

Example 3: Calculating Average Salary by Department

SELECT department_id, AVG(salary) AS avg_salary
FROM employees
GROUP BY department_id;

This query calculates the average salary for each department in the employees table.

---

Conclusion

The SQL 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 performing various aggregate operations and data analysis in a relational database.