How to Use SELECT With Aggregate Functions in SQL?

SQL aggregate functions are essential tools for summarizing and processing data. These functions help us perform calculations on a set of values to produce a single result, such as SUM, COUNT, AVG, MAX, and MIN. These functions work with the SELECT statement to process data and derive meaningful insights.

In this article, we will explain each of these five SQL aggregate functions, explain their purpose, and demonstrate how to use them with real-world examples.

Steps to Demonstrate SELECT With Aggregate Functions

We will use the END_SEM table within the GeeksForGeeks database for our demonstration. Let's first create the necessary database and table, populate it with data, and then explore aggregate functions.

Step 1: Create the Database

Before we start working with SQL aggregate functions, let’s create a database called GeeksForGeeks.

CREATE DATABASE GeeksForGeeks

Output

Step 2: Use the Database

Next, let’s select the GeeksForGeeks database for use. This is where we will store the data for our table For this use the below command.

Query:

USE GeeksForGeeks

Output

Step 3: Create the END_SEM Table

Create a table END_SEM inside the database GeeksForGeeks. This table has 3 columns namely S_NAME, ROLL, and MARKS containing the name, roll number, and marks scored by various students.

Query:

CREATE TABLE END_SEM(
S_NAME VARCHAR(20),
ROLL INT,
MARKS INT);

Output

Step 4: Describe the Structure of the Table

To check the structure of the END_SEM table, we can use the SP_COLUMNS stored procedure. This will display the column names and data types of the table.

Query:

EXEC SP_COLUMNS END_SEM;

Output

Step 5: Insert Data into the END_SEM Table

Now, let’s insert some sample data into the END_SEM table. We will add multiple rows with student names, roll numbers, and their marks.

Query:

INSERT INTO END_SEM VALUES('ABE',1,99);
INSERT INTO END_SEM VALUES('SAM',2,87);
INSERT INTO END_SEM VALUES('DARREN',3,74);
INSERT INTO END_SEM VALUES('RACHEL',4,91);
INSERT INTO END_SEM VALUES('DWIGHT',5,56);
INSERT INTO END_SEM VALUES('ANGELA',6,45);
INSERT INTO END_SEM VALUES('CREED',7,33);
INSERT INTO END_SEM VALUES('GABE',8,64);
INSERT INTO END_SEM VALUES('KELLY',9,49);
INSERT INTO END_SEM VALUES('ERIN',10,80);

Output

Step 6: Display All Rows in the Table

To view the data in the END_SEM table, we can use a SELECT query to fetch all the rows.

Query:

SELECT * FROM END_SEM;

Output

Using SELECT With Aggregate Functions

Now, let’s explain how to use the five primary SQL aggregate functions: SUM, COUNT, AVG, MAX, and MIN. These functions are applied to the MARKS column of the END_SEM table to demonstrate their use.

1. SUM() Function

The SUM() function calculates the total sum of the values in a specified column. It is commonly used to get the total of numerical data.

Query:

SELECT SUM(MARKS) AS "SUM OF MARKS" FROM END_SEM;

Output

2. COUNT() Function

The COUNT() function counts the number of rows that contain non-NULL values in a specified column. If we want to count all rows, including those with NULL values, use COUNT(*).

Query:

SELECT COUNT(MARKS) AS "COUNT OF
MARKS" FROM END_SEM;

Output:

3. AVG() Function

The AVG() function calculates the average (mean) of the values in a specified column. The AVG is calculated by dividing the SUM() of the values by the COUNT().

Query 1:

SELECT AVG(MARKS) AS "AVERAGE OF MARKS" FROM END_SEM;

Output

Query 2:

SELECT SUM(MARKS)/COUNT(MARKS) AS "AVERAGE OF MARKS" FROM END_SEM;

Output

4. MAX() Function

The MAX() function returns the maximum value from a specified column. This is useful when we need to identify the highest value in a column.

Query:

SELECT MAX(MARKS) AS "MAXIMUM OF MARKS" FROM END_SEM;

Output

5. MIN() Function

The MIN() function returns the minimum value from a specified column. It is useful for identifying the lowest value in a column.

Query:

SELECT MIN(MARKS) AS "MINIMUM OF MARKS" FROM END_SEM;

Output

Conclusion

SQL aggregate functions SUM, COUNT, AVG, MAX, and MIN are indispensable for data aggregation and analysis. They allow us to efficiently compute totals, averages, counts, and find the maximum or minimum values in our data. Mastering these functions enhances our ability to work with large datasets and simplifies complex queries. Incorporate these techniques into our SQL programming for efficient and powerful database management.