"Simplify your Queries: A Guide to SQL Aggregate Functions"

Aggregate Functions in SQL perform some calculations on more than one value to return a single value. SQL has many aggregate functions, including average, count, sum, min, and max. It can operate on multiple rows or multiple pieces of data at once to tell us the total, minimum, maximum and count .All aggregate functions ignore NULL values while calculating, except the Count function.

Introduction:

An Aggregate Functions in SQL returns one value after calculating multiple columns value. We often use aggregate functions with the SELECT statement’s GROUPBY and HAVING clauses.

Five types of SQL AGGREGATE functions:

• Count()

• sum()

• avg()

• min()

• max()

Column References:

In SQL, aggregate functions are crucial in calculating a set of values and returning a single value. These Functions are beneficial when dealing with large datasets. When using aggregate functions in SQL, it is essential to understand column references. A column reference is a name containing the data you want to aggregate. To use an aggregate function with a column reference, specify the column’s name in the function’s parentheses.

SELECT AVG(stock_quantity)

From books;

Aggregate functions in SQL are important to anyone working with large amount of data and trying to seek valuable insight.

1. COUNT() Function

   The count() function returns the number of rows in a database table.

Example:

We will demonstrate the ‘Books table’ from the sample database.

The following SQL statement fetches the number of rows in the table.

SELECT COUNT(*) FROM books;

This will produce the following result.

The following SQL statement fetches the number author first_name in the table.

SELECT COUNT(author_fname) FROM books;

This will produce the following result.

The below-given command will display distinct(unique) count of author first_name.

SELECT COUNT(DISTINCT author_fname) FROM books;

This will produce the following result.

one more example,

SELECT COUNT(*) FROM books WHERE title LIKE '%the%';

This will produce the following result.

GROUP BY summarizes or aggregates identical data into single rows.

Let’s look at how we can use GROUP BY and ORDER BY functions with the COUNT function.

SELECT author_lname,COUNT(*) AS books_written
FROM books
GROUP BY author_lname
ORDER BY books_written DESC;

This will produce the following result.

2. SUM() Function

The SUM() function returns the total sum of column.

The following SQL statement is to sum all the pages each author has written.

SELECT author_lname, COUNT(*), SUM(pages)
FROM books
GROUP BY author_lname;

This will produce the following result.

3. AVG() Function

The AVG() function calculates the average of set of values.

The following SQL command calculates the average stock quantity for books released in the same year.

SELECT 
    released_year, 
    AVG(stock_quantity), 
    COUNT(*) FROM books
GROUP BY released_year;

This will produce the following result.

4.MIN() Function

The MIN() aggregate function returns the lowest value(minimum) in a set of non-NULL values.

The following SQL command find the year of each author published their first book

SELECT author_lname, MIN(released_year) FROM books GROUP BY author_lname;

This will produce the following result.

5. Max() Function

The MAX() aggregate function returns the highest value(maximum) in a set of non-NULL values.

The following SQL command find the year of each author published their last book

SELECT author_lname, MAX(released_year) FROM books GROUP BY author_lname;

This will produce the following result.

6.Subqueries:

It is a query within a larger query. It is located within the WHERE clause of their enclosing statement. Their function is to set the search conditions for the WHERE clause. Each kind of subquery produces a different result.

Some subqueries produce a list of values that is then used as input by the enclosing statement. Other subqueries produce a single value that the enclosing statement then evaluates with a comparison operator. A third kind of subquery returns a values of true or false.

Example:

The following SQL command ,first it executes the sub-query (find max number of pages from books table )and then select title and pages from the output of subquery.

SELECT title, pages FROM books
WHERE pages = (SELECT MAX(pages) FROM books);

This will produce the following result.

7. Grouping By Multiple Columns:

   You can GROUP BY two columns in SQL. TO group by multiple categories, just separate column names with commas. User can apply aggregate functions like sum, count, avg, min and max to each group, make it easier to perform data analysis.

   Example:

SELECT CONCAT(author_fname, ' ', author_lname) AS author,  COUNT(*)
FROM books
GROUP BY author;

This will produce the following result.

8. MIN And Max With GROUPBY

   GROUPBY clause along with sql Max() can be used to find the maximum value of a column over each group and MIN() can be used to fine the minimum value of a column over each group.

   Example:

SELECT author_lname, author_fname,
COUNT(*) as books_written, 
MAX(released_year) AS latest_release,
MIN(released_year)  AS earliest_release  
FROM books 
GROUP BY author_lname, author_fname;

This will produce the following result.

9. GROUPBY with HAVING clause:

   The HAVING clause is a filter that acts like a WHERE clause, but on groups of rows rather than on individual rows.

   Example:

10. The following SQL command ,Group by author followed by Having clause sum(pages) >=200

SELECT CONCAT(author_fname, ' ', author_lname) AS author,
SUM(pages) AS Total_pages
FROM BOOKS
GROUP BY author 
HAVING Total_pages>=200;

This will produce the following result.

CONCLUSION:

Aggregate Functions are easily to calculate large datasets quickly and provide insights from complex data by calculating single values like sum ,avg, min, max. As per our title it simplifies complex queries and improve query performance. I hope you will be clear about aggregate functions in SQL.

Thanks for reading!