Aggregation Functions

Aggregation functions are used to perform mathematical operations on data values of a relation. Some of the common aggregation functions used in SQL are:

• COUNT: Count function is used to count the number of rows in a relation. e.g;

SELECT COUNT (PHONE) FROM STUDENT;

• SUM: SUM function is used to add the values of an attribute in a relation. e.g;

SELECT SUM(AGE) FROM STUDENT; 

In the same way, MIN, MAX and AVG can be used.  As we have seen above, all aggregation functions return only 1 row. AVERAGE: It gives the average values of the tupples. It is also defined as sum divided by count values.

Syntax:

AVG(attributename)

OR

SUM(attributename)/COUNT(attributename)

The above mentioned syntax also retrieves the average value of tupples.

• MAXIMUM:It extracts the maximum value among the set of tupples.

Syntax:

MAX(attributename)

• MINIMUM:It extracts the minimum value amongst the set of all the tupples.

Syntax:

MIN(attributename)

• GROUP BY:Group by is used to group the tuples of a relation based on an attribute or group of attribute. It is always combined with aggregation function which is computed on group. e.g.;

SELECT ADDRESS, SUM(AGE) FROM STUDENT
GROUP BY (ADDRESS);

In this query, SUM(AGE) will be computed but not for entire table but for each address. i.e.; sum of AGE for address DELHI(18+18=36) and similarly for other address as well. The output is:

If we try to execute the query given below, it will result in error because although we have computed SUM(AGE) for each address, there are more than 1 ROLL_NO for  each address we have grouped. So it can’t be displayed in result set. We need to use aggregate functions on columns after SELECT statement to make sense of the resulting set whenever we are using GROUP BY.

SELECT ROLL_NO, ADDRESS, SUM(AGE) FROM STUDENT
GROUP BY (ADDRESS); 

NOTE:

An attribute which is not a part of GROUP BY clause can’t be used for selection. 
Any attribute which is part of GROUP BY CLAUSE can be used for selection but it is not mandatory. 
But we could use attributes which are not a part of the GROUP BY clause in an aggregate function. 

Structured Query Language is a standard Database language that is used to create, maintain, and retrieve the relational database. In this article, we will discuss this in detail about SQL. Following are some interesting facts about SQL. Let’s focus on that.

SQL is case insensitive. But it is a recommended practice to use keywords (like SELECT, UPDATE, CREATE, etc.) in capital letters and use user-defined things (like table name, column name, etc.) in small letters.

We can write comments in SQL using “–” (double hyphen) at the beginning of any line. SQL is the programming language for relational databases (explained below) like MySQL, Oracle, Sybase, SQL Server, Postgre, etc. Other non-relational databases (also called NoSQL) databases like MongoDB, DynamoDB, etc. do not use SQL.

Although there is an ISO standard for SQL, most of the implementations slightly vary in syntax. So we may encounter queries that work in SQL Server but do not work in MySQL.