Hey, readers! In this article we will be focusing on some of the extensively used aggregate functions of SQL — sum(), count() and avg() function.
SQL sum() function
The SQL sum() function does the summation of all the values of a particular column of a respective table and returns the value obtained as the summation of it.
Syntax:
|
1 2 3 |
SELECT SUM(column) FROM Table WHERE condition; |
In the above syntax, the WHERE clause is used to act upon a condition provided ahead of it.
Example 1:
|
1 2 3 4 5 6 7 8 |
create table Info(id integer, Cost integer); insert into Info(id, Cost) values(1, 100); insert into Info(id, Cost) values(2, 50); insert into Info(id, Cost) values(3, 65); insert into Info(id, Cost) values(4, 97); insert into Info(id, Cost) values(5, 12); SELECT SUM(Cost) FROM Info; |
In the above example, we have created a Table ‘Info’ containing columns ‘id’ and ‘Cost’. Further, we have used SQL sum() function to depict the summation of all the data values present in the column ‘Cost’.
Output:
|
1 |
324 |
Example 2:
|
1 2 3 4 5 6 7 8 9 |
create table Info(id integer, Cost integer); insert into Info(id, Cost) values(1, 100); insert into Info(id, Cost) values(2, 50); insert into Info(id, Cost) values(3, 65.45); insert into Info(id, Cost) values(4, 97); insert into Info(id, Cost) values(5, 12); SELECT SUM(Cost) FROM Info WHERE Cost>20; |
In this example, SQL WHERE clause is used to select only those data values of the column ‘Cost’ that have values greater than 20. These selected data values are passed to the SQL sum() function for summation of the values.
Output:
|
1 |
312 |
SQL sum() function with GROUP BY clause
The SQL GROUP BY clause is used along SELECT statement to group identical data values against some particular group values.
Syntax of SQL GROUP BY clause:
|
1 2 |
SELECT * from Table GROUP BY Column; |
Example:
|
1 2 3 4 5 6 7 8 9 10 |
create table Info(id integer, Cost integer, city varchar(200)); insert into Info(id, Cost,city) values(1, 100,"Pune"); insert into Info(id, Cost,city) values(2, 50, "Satara"); insert into Info(id, Cost,city) values(3, 65,"Pune"); insert into Info(id, Cost,city) values(4, 97,"Mumbai"); insert into Info(id, Cost,city) values(5, 12,"USA"); SELECT city, SUM(Cost) FROM Info GROUP BY city; |
In this example, the GROUP BY clause is been used to group the identical data values of column ‘City’ by groups.
Further, these set of data values are passed to SQL sum() function and it performs summation of elements of those groups separately.
Output:
|
1 2 3 4 |
Mumbai 97 Pune 165 Satara 50 USA 12 |
SQL count() function
SQL count() function counts the total number of data values present in a particular column passed to it.
Syntax:
|
1 2 3 |
SELECT COUNT(column) FROM Table WHERE condition; |
Example 1:
|
1 2 3 4 5 6 7 8 9 10 |
create table Info(id integer, Cost integer, city varchar(200)); insert into Info(id, Cost,city) values(1, 100,"Pune"); insert into Info(id, Cost,city) values(2, 50, "Satara"); insert into Info(id, Cost,city) values(3, 65,"Pune"); insert into Info(id, Cost,city) values(4, 97,"Mumbai"); insert into Info(id, Cost,city) values(5, 12,"USA"); SELECT count(id) FROM Info; |
In the above example, we have displayed a count of data values present in the column ‘id’ using SQL count() function.
Output:
|
1 |
5 |
Example 2:
|
1 2 3 |
SELECT count(id) FROM Info WHERE city = "Pune"; |
Considering the Table of Example 1, we have used SQL count() function alongside WHERE clause to count only those data values that happen to belong to the City ‘Pune’.
Output:
|
1 |
2 |
SQL count() function with GROUP BY clause
SQL count() function can be clubbed alongside GROUP BY clause to get the count of data values against a particular group of data.
Example:
|
1 2 3 4 5 6 7 8 9 10 |
create table Info(id integer, Cost integer, city varchar(200)); insert into Info(id, Cost,city) values(1, 100,"Pune"); insert into Info(id, Cost,city) values(2, 50, "Satara"); insert into Info(id, Cost,city) values(3, 65,"Pune"); insert into Info(id, Cost,city) values(4, 97,"Mumbai"); insert into Info(id, Cost,city) values(5, 12,"USA"); SELECT city,count(id) FROM Info GROUP BY city; |
In the above example, we have used SQL GROUP BY clause to group the data values by the column ‘city’. After which, we have used the SQL count() function to calculate the number of data values associated to that particular groups formed.
Output:
|
1 2 3 4 |
Mumbai 1 Pune 2 Satara 1 USA 1 |
SQL avg() function
SQL avg() function estimates the average of data values of a particular column passed to it.
Syntax:
|
1 2 3 |
SELECT AVG(column) FROM Table WHERE condition; |
Example 1:
|
1 2 |
SELECT avg(Cost) FROM Info; |
Referring to the Table created in the above section, we have used SQL avg() function to calculate the average of data values of the column ‘Cost’.
Output:
|
1 |
64.8000 |
Example 2:
|
1 2 3 |
SELECT avg(Cost) FROM Info WHERE city = "Pune"; |
In this example, we have calculate the average value of the data elements which belong to the city ‘Pune’.
Output:
|
1 |
82.5000 |
SQL avg() function with GROUP BY clause
SQL avg() function along with GROUP BY clause is used to calculate the average of data values against respective groups of data.
Example:
|
1 2 3 4 5 6 7 8 9 10 11 |
create table Info(id integer, Cost integer, city varchar(200)); insert into Info(id, Cost,city) values(1, 100,"Pune"); insert into Info(id, Cost,city) values(2, 50, "Satara"); insert into Info(id, Cost,city) values(3, 65,"Pune"); insert into Info(id, Cost,city) values(4, 97,"Mumbai"); insert into Info(id, Cost,city) values(5, 12,"USA"); SELECT city,avg(Cost) FROM Info GROUP BY city; |
We have grouped the data values along the column ‘city’ and then calculated the average value of the data belonging to identical city.
Output:
|
1 2 3 4 |
Mumbai 97.0000 Pune 82.5000 Satara 50.0000 USA 12.0000 |
Conclusion
By this, we have come to the end of this topic. I hope all the queries have been answered by the above content.
Please feel free to comment below in case you come across a doubt.