SQL Aggregate Functions: All That You Need to Know!

Explore the world of SQL Aggregate Functions with this recipe! Learn all about these functions that help analyze and summarize data effectively. | ProjectPro

Recipe Title - SQL Aggregate Functions: All That You Need to Know! 

SQL Aggregate Functions are the secret ingredients that transform ordinary queries into powerful data analysis tools. Whether you're a seasoned SQL developer or a novice explorer in the world of databases, understanding and leveraging aggregate functions can significantly enhance your ability to extract meaningful insights from your data.

What are SQL Aggregate Functions?

In SQL, aggregate functions operate on sets of values and return a single value summarizing the input data. They are particularly useful for performing calculations on data within a specific column, allowing you to obtain valuable statistics and metrics. Let's dive into some of the most commonly used SQL aggregate functions:

SQL Aggregate functions List 

  • Max - The MAX function retrieves the highest value from a specified column. 

  • Min - The MIN function retrieves the lowest value from a specified column. 

  • Count - The COUNT function tallies the number of rows in a specified column. 

  • Avg - The AVG function calculates the average value of a specified column. 

  • Sum - The SUM function adds up all the values in a specified column.

SQL Function MIN & MAX - How to use Min and Max functions in SQL?

Min and Max functions are a part of the aggregate functions of SQL. As the name suggests, the MIN function returns the least value among the list of values passed as input, and the MAX function returns the maximum value.

Syntax:

For SQL Min function
SELECT MIN(column_name) FROM table_name WHERE condition;

For SQL Max function
SELECT MAX (column_name) FROM table_name WHERE condition;

Let us make use of the Min and Max functions to find the minimum and maximum income in the customers table.

For minimum income
Code:

SELECT MIN(income) FROM customers; 

SQL MIN Function

Output:

+-------------+

| MIN(income) |

+-------------+

|    10000000 |

+-------------+

1 row in set (0.02 sec)

For maximum income
Code:

SELECT MAX(income) FROM customers;

SQL MAX Function

Output:

+-------------+

| MAX(income) |

+-------------+

|    90000000 |

+-------------+

1 row in set (0.00 sec)

SQL Function COUNT - How to use Count Function in SQL?

The count is an aggregate function of SQL that returns the number of rows meeting the specified condition.

Syntax:
SELECT COUNT(column_name) FROM table_name
WHERE condition;

Let us take a count of customers with ages greater than 30.
Code:

SELECT COUNT(age) FROM customers WHERE age>30;

SQL Function COUNT

Output:

+------------+

| COUNT(age) |

+------------+

|          1 |

+------------+

1 row in set (0.01 sec)

There is only 1 customer in the customers table who has an age greater than 30 years.

SQL Function Avg - How to use the Avg function in SQL?

The AVG function in SQL simply returns the average of the values that meet the specified condition. It is also an aggregate function of SQL.

Syntax:
SELECT AVG(column_name) FROM table_name WHERE condition;

Let us check the average age of the customers.

Code:

SELECT AVG(age) FROM customers;

SQL Function Avg

Output:

+----------+

| AVG(age) |

+----------+

|  29.8000 |

+----------+

1 row in set (0.00 sec)

On average, a customer is around 30 years old.

SQL Function SUM - How to use the Sum function in SQL?

The SUM function in SQL is yet another aggregate function. We can use it alongside the SELECT statement as follows –

Syntax:
SELECT SUM(column_name) FROM table_name WHERE condition;

Let us check the sum of the income of all the customers.

Code:

SELECT SUM(income) FROM customers;

SQL Function SUM

Output:

+-------------+

| SUM(income) |

+-------------+

|   312000000 |

+-------------+

1 row in set (0.00 sec)

Master SQL Aggregations with ProjectPro! 

SQL Aggregate Functions are versatile tools that empower you to analyze and extract valuable insights from your database. Whether you are calculating summary statistics, grouping data, or using nested functions, mastering these functions is essential for anyone working with SQL. By incorporating these functions into your queries, you'll unlock the full potential of your data analysis capabilities.

Now that you have a solid understanding of SQL Aggregate Functions, start integrating them into your queries to make your data work for you! Check out ProjectPro Repository to get access to 270+ solved end-to-end projects on data science and big data. 

What Users are saying..

profile image

Ed Godalle

Director Data Analytics at EY / EY Tech
linkedin profile url

I am the Director of Data Analytics with over 10+ years of IT experience. I have a background in SQL, Python, and Big Data working with Accenture, IBM, and Infosys. I am looking to enhance my skills... Read More

Relevant Projects

Hands-On Approach to Regression Discontinuity Design Python
In this machine learning project, you will learn to implement Regression Discontinuity Design Example in Python to determine the effect of age on Mortality Rate in Python.

MLOps Project for a Mask R-CNN on GCP using uWSGI Flask
MLOps on GCP - Solved end-to-end MLOps Project to deploy a Mask RCNN Model for Image Segmentation as a Web Application using uWSGI Flask, Docker, and TensorFlow.

Azure Deep Learning-Deploy RNN CNN models for TimeSeries
In this Azure MLOps Project, you will learn to perform docker-based deployment of RNN and CNN Models for Time Series Forecasting on Azure Cloud.

Word2Vec and FastText Word Embedding with Gensim in Python
In this NLP Project, you will learn how to use the popular topic modelling library Gensim for implementing two state-of-the-art word embedding methods Word2Vec and FastText models.

End-to-End Snowflake Healthcare Analytics Project on AWS-1
In this Snowflake Healthcare Analytics Project, you will leverage Snowflake on AWS to predict patient length of stay (LOS) in hospitals. The prediction of LOS can help in efficient resource allocation, lower the risk of staff/visitor infections, and improve overall hospital functioning.

Stock Price Prediction Project using LSTM and RNN
Learn how to predict stock prices using RNN and LSTM models. Understand deep learning concepts and apply them to real-world financial data for accurate forecasting.

Avocado Machine Learning Project Python for Price Prediction
In this ML Project, you will use the Avocado dataset to build a machine learning model to predict the average price of avocado which is continuous in nature based on region and varieties of avocado.

Build a CNN Model with PyTorch for Image Classification
In this deep learning project, you will learn how to build an Image Classification Model using PyTorch CNN

NLP Project for Multi Class Text Classification using BERT Model
In this NLP Project, you will learn how to build a multi-class text classification model using using the pre-trained BERT model.

NLP and Deep Learning For Fake News Classification in Python
In this project you will use Python to implement various machine learning methods( RNN, LSTM, GRU) for fake news classification.