8 Powerful SQL Aggregate Functions You Must Master for Effective Data Analysis

SQL Aggregate Functions

• SQL Aggregate Functions are powerful programming functions used to manipulate multiple pieces of data stored in a database.
• In other words, they allow users to calculate and analyze large data sets by generating a single quantity from a group of rows.
• SQL Aggregate functions, are the primary source for report, data analysis, and discovering information from linked databases.
• Here, we will discuss the basic SQL aggregate functions used in reports in a more detailed manner, giving examples for better understanding.

1. COUNT() Function

• The COUNT() function is used to count the number of rows in a table or non-NULL values in a specific column. One of the most common uses of aggregate functions is counting.
• Syntax:

SELECT COUNT(column_name) FROM table_name;

• Example:

SELECT COUNT(*) FROM employees;

• In this query, all the rows of the employees’ table are counted, inclusive of the nulls in one of the columns
• Should you choose the count of only non-NULL values in a particular column, you can employ:

SELECT COUNT(email) FROM employees;

• Now, only non-NULL email addresses in the employees table are counted.

Notes:

• COUNT(*) counts all the rows, and COUNT(column_name) counts only non-NULL values in an indicated column.

2. SUM() Function

• The SUM() function computes the grand total sum of a numeric column. It is the main usage to get the total sales, total salary or any other total value from a column.
• Syntax:

SELECT SUM(column_name) FROM table_name;

• Example:

SELECT SUM(salary) FROM employees;

• It determines the entire salary that all the employees obtain in the employees table.

Notes:

• The SUM() function is compatible only with numeric data types such as INT, DECIMAL, FLOAT, and so on.
• The null values are not taken into consideration while summing up.

3. AVG() Function

• The AVG() function is used for computing the average or mean value of the numeric data in a specific column. In the context of deductions, it assigns average values to information such as average salary, average age, and so on.
• Syntax:

SELECT AVG(column_name) FROM table_name;

• Example:

SELECT AVG(age) FROM employees;

• This query is for finding out the average age of all the employees in the employees table.

Notes:

• Just like SUM(), the AVG() will run only on that column which contains the numeric data.
• The null values will be ignored in the data while the average calculation is being carried out.

4. MIN() Function

• The MIN() function finds the minimal value in a given column. It is often used to determine the earliest date, the cheapest item, or the least amount of inventory in a dataset.
• Syntax:

SELECT MIN(column_name) FROM table_name;

• Example:

SELECT MIN(salary) FROM employees;

• This query provides the minimum salary for the employees table.

Notes:

• The MIN() function operates with any data types like the numeric, date, and text ones. It gives the first in the alphabet from the text columns.

5. MAX() Function

• The MAX() function displays the maximal value of the column. It is employed to get the highest wage, the most recent date, or the largest order quantity, for example.
• Syntax:

SELECT MAX(column_name) FROM table_name;

• Example:

SELECT MAX(salary) FROM employees;

• This query returns the highest salary in the employees table.

Notes:

• Just like MIN(), the MAX() function also operates with numerics, dates, and strings. For text columns, it gives the alphabetically last value.

6. GROUP_CONCAT() Function (MySQL) / STRING_AGG() Function (PostgreSQL, SQL Server)

• The GROUP_CONCAT() function (in MySQL) or STRING_AGG() function (in PostgreSQL and SQL Server) is a utility function that concatenates data-based values from multiple rows into one single string. This is used when you are supposed to join text data, for example, a list of all employees in a department.
• Syntax (MySQL):

SELECT GROUP_CONCAT(column_name) FROM table_name;

• Example (MySQL):

SELECT GROUP_CONCAT(first_name) FROM employees;

• The last query puts together all the employees’ first names into a single string.
• Syntax (PostgreSQL and SQL Server):

SELECT STRING_AGG(column_name, ', ') FROM table_name;

• Example (PostgreSQL/SQL Server):

SELECT STRING_AGG(first_name, ', ') FROM employees;

• It is possible to concatenate employee first names by means of a comma, wherein, each name will be separated by a comma.

Notes:

• The GROUP_CONCAT() function in MySQL usually joins all the output by commas but it can be turned to another one of your choosing.
• The STRING_AGG() function is a feature that allows you to choose a custom delimiter.

7. FIRST() and LAST() Functions (In some databases)

• Using the FIRST() and LAST() functions is an alternative method of ordering data. The FIRST() operation will always return the first value in a specific field, whereas the LAST() operation will return the last value.
• Syntax:

SELECT FIRST(column_name) FROM table_name;

SELECT LAST(column_name) FROM table_name;

• Example:

SELECT FIRST(salary) FROM employees ORDERBY hire_date ASC;

• In this query, the function GET_FIRST is used to achieve the second oldest customer.

Notes:

• It should be noted that these functions are database system-dependent features, i.e., they can be used in the MySQL database. However, a database like SQL Server may not explicitly support these functions. You might use LIMIT or TOP in other SQL flavors.

8. Combination of Aggregate Functions

• SQL can be used to combine multiple aggregate functions in one query, which is highly beneficial in the generation of summary reports.
• Example:

SELECT department, COUNT(*) AS num_employees, AVG(salary) AS avg_salary, MAX(salary) AS max_salary

FROM employees

GROUP BY department;

• This query gives a summary for each department by displaying the number of employees, the average salary, and the highest salary in each department.

Using Aggregate Functions with HAVING

• While the WHERE clause is used to filter out rows before the summation, the HAVING clause is used to filter out results after the summation have been performed.
• Example:

SELECT department, COUNT(*) AS num_employees

FROM employees

GROUP BY department

HAVING COUNT(*) > 5;

• This query groups employees by department and only includes departments with more than 5 employees.