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

Gautam Vermani

Data Consultant at Confidential
linkedin profile url

Having worked in the field of Data Science, I wanted to explore how I can implement projects in other domains, So I thought of connecting with ProjectPro. A project that helped me absorb this topic... Read More

Relevant Projects

Build a Music Recommendation Algorithm using KKBox's Dataset
Music Recommendation Project using Machine Learning - Use the KKBox dataset to predict the chances of a user listening to a song again after their very first noticeable listening event.

Deploy Transformer BART Model for Text summarization on GCP
Learn to Deploy a Machine Learning Model for the Abstractive Text Summarization on Google Cloud Platform (GCP)

Build an AI Chatbot from Scratch using Keras Sequential Model
In this NLP Project, you will learn how to build an AI Chatbot from Scratch using Keras Sequential Model.

AWS Project to Build and Deploy LSTM Model with Sagemaker
In this AWS Sagemaker Project, you will learn to build a LSTM model on Sagemaker for sales forecasting while analyzing the impact of weather conditions on Sales.

Build a Face Recognition System in Python using FaceNet
In this deep learning project, you will build your own face recognition system in Python using OpenCV and FaceNet by extracting features from an image of a person's face.

Build a Multi Touch Attribution Machine Learning Model in Python
Identifying the ROI on marketing campaigns is an essential KPI for any business. In this ML project, you will learn to build a Multi Touch Attribution Model in Python to identify the ROI of various marketing efforts and their impact on conversions or sales..

ML Model Deployment on AWS for Customer Churn Prediction
MLOps Project-Deploy Machine Learning Model to Production Python on AWS for Customer Churn Prediction

Build Real Estate Price Prediction Model with NLP and FastAPI
In this Real Estate Price Prediction Project, you will learn to build a real estate price prediction machine learning model and deploy it on Heroku using FastAPI Framework.

Build a Collaborative Filtering Recommender System in Python
Use the Amazon Reviews/Ratings dataset of 2 Million records to build a recommender system using memory-based collaborative filtering in Python.

Llama2 Project for MetaData Generation using FAISS and RAGs
In this LLM Llama2 Project, you will automate metadata generation using Llama2, RAGs, and AWS to reduce manual efforts.