How to use SQL Aggregate Functions
With 9+ years in SEM and data automation, Piyush Nair helps businesses and individuals simplify processes using Google Sheets, Excel, Apps Script, and other productivity tools, turning data into actionable insights
How this article is generated : This article was written by a human author and AI is used in proofread.
Introduction
This article explains some basic SQL aggregate functions and demonstrates their practical application with examples.
When handling datasets, the calculation of aggregate values is frequently required. The most common basic functions include: SUM, COUNT, AVG, MIN, and MAX.
Importance of Group By syntax in SQL aggregate functions
When working with SQL, it is crucial to use the GROUP BY clause whenever you are retrieving rows based on distinct column values. Do this rather than just simple aggregate counts.
Employee Table Data contains 7 rows and 5 columns:
Example : I want total rows in employee table
Example : GROUP BY is used when you want count against distinct values of any column as in below image we are counting employees count against each distinct dept_id
SQL Aggregate Functions List
We will understand how to use some basic aggregate functions in SQL. functions like – SUM, COUNT, AVG, MIN, and MAX.
SUM :
Employee Table Data:
Situation 1 : How to get total SUM of all rows in salary column
Example :
Explanation:
Select then write SUM(column name) As New column name you want to appear in output
FROM table name
Situation 2 : How to get total SUM of rows for against each dept_id
Example :
Explanation:
Select 1st column as dept_id then 2nd column is sum of salary but the column name will be totalsalary in output
From employees
Group by depy_id
COUNT :
Employee Table Data:
Situation 1 : How to get total COUNT of all rows using salary column
Example :
Explanation:
Select then write COUNT(column name) As New column name you want to appear in output
FROM table name
Situation 2 : How to get total COUNT of rows for against each dept_id
Example :
Explanation:
Select 1st column as dept_id then 2nd column is count of salary but the column name will be totalcountofsalaryrow in output
From employees
Group by depy_id
AVG (Average) :
Employee Table Data:
Situation 1 : How to get average of all rows of salary column
Example :
Explanation:
Select then write average(column name) As New column name you want to appear in output
FROM table name
Situation 2 : How to get total average of rows for against each dept_id
Example :
Explanation:
Select 1st column as dept_id then 2nd column is average of salary but the column name will be taveragesalary in output
From employees
Group by depy_id
Min (Minimum) and Max (Maximum) :
Employee Table Data:
Note : Column data should contain a number data type
Situation 1 : How to get minimum and maximum of all value in salary column
Example :
Explanation:
Select then write min(column name) or max(column name) As New column name you want to appear in output
FROM table name
Situation 2 : How to get minimum and maximum salary of each dept_id
Example :
Explanation:
Select 1st column as dept_id then 2nd column is average of salary but the column name will be taveragesalary in output
From employees
Group by depy_id
More Blogs!!
| Learn Google Sheets – Basic to Advance |
| How Does VLOOKUP Work ? |
| Everything about Pivot Table in Google Sheets |