MySQL

In MySQL, aggregate functions like COUNT(), SUM(), AVG(), MIN(), and MAX() are used to perform calculations on a set of values, often with the GROUP BY clause. These functions help you summarize data, calculate totals, averages, and find the minimum or maximum values in a set of rows. They are essential when working with reports and data analysis in relational databases.

Here’s an explanation of each aggregate function, with examples to illustrate their usage:

1. Using COUNT():

   • The COUNT() function returns the number of rows that match a specified condition. It can be used to count all rows or non-NULL values in a specific column.
   • Syntax:

   SELECT COUNT(*) AS total_employees 
   FROM employees;

   • This query returns the total number of employees in the employees table.

   Additional Points:

   • You can count non-NULL values in a specific column:

   SELECT COUNT(manager_id) AS total_managers 
   FROM employees;

2. Using SUM():

   • The SUM() function calculates the total sum of a numeric column.
   • Example:

   SELECT SUM(salary) AS total_salary 
   FROM employees;

   • This query returns the total salary of all employees.

   Additional Points:

   • You can use SUM() in combination with GROUP BY to calculate totals by groups:

   SELECT department_id, SUM(salary) AS total_salary 
   FROM employees 
   GROUP BY department_id;

3. Using AVG():

   • The AVG() function calculates the average value of a numeric column.
   • Example:

   SELECT AVG(salary) AS average_salary 
   FROM employees;

   • This query returns the average salary of all employees.

   Additional Points:

   • You can calculate the average salary per department:

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

4. Using MIN():

   • The MIN() function returns the smallest value from a column.
   • Example:

   SELECT MIN(salary) AS minimum_salary 
   FROM employees;

   • This query returns the lowest salary among all employees.

   Additional Points:

   • You can use MIN() to find the minimum value within a group:

   SELECT department_id, MIN(salary) AS minimum_salary 
   FROM employees 
   GROUP BY department_id;

5. Using MAX():

   • The MAX() function returns the largest value from a column.
   • Example:

   SELECT MAX(salary) AS maximum_salary 
   FROM employees;

   • This query returns the highest salary among all employees.

   Additional Points:

   • You can use MAX() to find the maximum salary in each department:

   SELECT department_id, MAX(salary) AS maximum_salary 
   FROM employees 
   GROUP BY department_id;

6. Combining Multiple Aggregate Functions:

   • You can combine multiple aggregate functions in a single query to calculate different summaries.
   • Example:

   SELECT department_id, COUNT(*) AS num_employees, AVG(salary) AS avg_salary, MIN(salary) AS min_salary, MAX(salary) AS max_salary 
   FROM employees 
   GROUP BY department_id;

   • This query returns the number of employees, average salary, minimum salary, and maximum salary for each department.

7. Using Aggregate Functions with HAVING:

   • You can use the HAVING clause to filter results based on aggregate values.
   • Example:

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

   • This query returns departments where the average salary exceeds 50,000.

8. Performance Considerations:

   • Using aggregate functions on large datasets can be resource-intensive, so indexing key columns can improve query performance.

These examples demonstrate how to use the most common aggregate functions in MySQL for data analysis and reporting. They allow you to summarize data efficiently, making them essential tools in working with relational databases.

To gain complete access, login with gmail or outlook, no need of signup, click here

SELECT COUNT(*)
FROM org_employee;

SELECT COUNT(DISTINCT department) 
FROM org_employee;

SELECT SUM(salary) 
FROM org_employee;

SELECT AVG(salary) 
FROM org_employee;

SELECT MIN(salary) 
FROM org_employee;

SELECT MAX(salary) 
FROM org_employee;

SELECT a.* 
FROM org_employee a 
INNER JOIN (SELECT MIN(salary) as min_salary FROM org_employee) b 
	ON a.salary = b.min_salary
;

SELECT department
    SUM(salary)
FROM org_employee
GROUP BY department
ORDER BY SUM(salary) DESC;

SELECT department
    COUNT(*)
FROM org_employee
GROUP BY department;

SELECT department
    MAX(salary)
FROM org_employee
GROUP BY department;

SELECT department
    MIN(salary)
FROM org_employee
GROUP BY department;

Example 1:

Here are few SQL examples showcasing the utilization of COUNT, SUM, and MAX SQL functions.

Example 1 - Raw data from employee table

Example 1 - COUNT

SELECT COUNT(*) FROM org_employee;

Example 1 - Query data mapping

Essentially, you are required to determine the count of rows in the table.

Example 1 - Query Output

Example 2 - COUNT DISTINCT

SELECT COUNT(DISTINCT department) FROM org_employee;

Example 2 - Query data mapping

The green-colored boxes that have been highlighted represent unique department names that will be considered for the count.

Example 2 - Query Output

Example 3 - SUM by categroy using GROUP BY

SELECT department, SUM(salary) FROM org_employee GROUP BY department ORDER BY SUM(salary) DESC;

Example 3 - Query data mapping

The salaries from each department are aggregated by department, summing them together.

Example 3 - Query Output

Example 4 - MAX by categroy using GROUP BY

SELECT department, MAX(salary) FROM org_employee GROUP BY department;

Example 4 - Query data mapping

Green-colored boxes represent the highest salary from each corresponding department.

Example 4 - Query Output