SQL Aggregate Functions (COUNT, SUM, AVG, GROUP BY)
SQL Aggregate Functions (COUNT, SUM, AVG, GROUP BY)
SQL Aggregate Functions are used to perform calculations on a set of values and return a single result. These functions are essential for data analysis, reporting, and summarizing large datasets in a database.
In this lesson, you will learn the most commonly used SQL aggregate functions such as COUNT, SUM, AVG, and how to use them with GROUP BY for better data insights.
What are SQL Aggregate Functions?
Aggregate functions perform operations on multiple rows and return a single value. They are widely used in relational databases like MySQL, PostgreSQL, and Microsoft SQL Server.
These functions are useful for:
- Data analysis
- Reporting
- Business insights
Types of SQL Aggregate Functions
1. COUNT()
COUNT() is used to count the number of rows in a table.
Syntax:
SELECT COUNT(*) FROM Students;
Example:
SELECT COUNT(*) FROM Students;
2. SUM()
SUM() is used to calculate the total of a numeric column.
Syntax:
SELECT SUM(column_name) FROM table_name;
Example:
SELECT SUM(Marks) FROM Students;
3. AVG()
AVG() calculates the average value of a numeric column.
Syntax:
SELECT AVG(column_name) FROM table_name;
Example:
SELECT AVG(Marks) FROM Students;
4. MIN()
MIN() returns the smallest value in a column.
Example:
SELECT MIN(Marks) FROM Students;
5. MAX()
MAX() returns the highest value in a column.
Example:
SELECT MAX(Marks) FROM Students;
GROUP BY Clause
GROUP BY is used with aggregate functions to group data based on one or more columns.
Syntax:
SELECT column_name, COUNT(*)
FROM table_name
GROUP BY column_name;
Example:
SELECT Course_ID, COUNT(*)
FROM Students
GROUP BY Course_ID;
This query counts the number of students in each course.
Real-World Example
In an e-commerce database:
- COUNT() → Total number of orders
- SUM() → Total sales
- AVG() → Average order value
- GROUP BY → Sales per category
Why SQL Aggregate Functions are Important
They help you:
- Analyze large datasets
- Generate reports
- Extract meaningful insights
- Improve decision-making
FAQs
What are SQL aggregate functions?
They are functions used to perform calculations on multiple rows and return a single value.
What does COUNT() do?
COUNT() returns the total number of rows.
What is GROUP BY in SQL?
GROUP BY is used to group rows based on a column.
Can we use GROUP BY with multiple columns?
Yes, GROUP BY can be used with multiple columns.
Where can I learn more courses like this?
Click here for more free courses