SQL sum(), count() and avg() functions

Filed Under: SQL
SQL Sum(), Count() And Avg() Function

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:

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:

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:

324

Example 2:

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:

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:

SELECT * from Table
GROUP BY Column;

Example:

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:

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:

SELECT COUNT(column) 
FROM Table
WHERE condition;

Example 1:

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:

5

Example 2:

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:

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:

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:

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:

SELECT AVG(column) 
FROM Table
WHERE condition;

Example 1:

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:

64.8000

Example 2:

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:

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:

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:

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.


References

Leave a Reply

Your email address will not be published. Required fields are marked *

close
Generic selectors
Exact matches only
Search in title
Search in content
Search in posts
Search in pages