How to Use SELECT With Aggregate Functions in SQL?

In SQL, mainly five aggregate functions are provided. These help us to process the table data in the desired way. All these are illustrated below. For this article, we will be using the Microsoft SQL Server as our database.

Step 1: Create a Database. For this use the below command to create a database named GeeksForGeeks.

Query:

CREATE DATABASE GeeksForGeeks

Output:

Step 2: Use the GeeksForGeeks database. For this use the below command.

Query:

USE GeeksForGeeks

Output:

Step 3: 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 END_sEM.

Query:

EXEC SP_COLUMNS END_SEM;

Output:

Step 5: Insert 5 rows into the END_SEM table.

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 the rows of the END_SEM table.

Query:

SELECT * FROM END_SEM;

Output:

There are five aggregate functions in SQL. All of them are demonstrated with their usage on the above newly created END_SEM table below:

• SUM Function: It returns the sum(addition) of all the entries of the column specified in the brackets after SUM.

Query:

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

Output:

• COUNT Function: It returns the count of(number of) non-null entries in the column specified in the brackets after COUNT. If we need this for all the columns then we need to put a * in the brackets after COUNT.

Query:

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

Output:

• AVG Function: It returns the average(mean) of all the entries of the column specified in the brackets after AVG. Average = Sum/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:

• MAX Function: It returns the maximum valued entry of all the entries in the column specified in the brackets after MAX.

Query:

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

Output:

• MIN Function: It returns the minimum valued entry of all the entries in the column specified in the brackets after MIN.

Query:

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

Output: