SQL COUNT(), SUM() and AVG() Functions

SQL COUNT, SUM And AVG Functions

This is a detailed tutorial of SQL COUNT(), SUM() and AVG() Functions. Learn to find count, sum, and the average of the database table column values.

Use of SQL COUNT(), SUM() and AVG() Functions

COUNT(), SUM() and AVG() are the simple mathematical functions in SQL that are used to calculate the total count, sum and the average of a database table column(s) values.

COUNT() function works on all table columns whether it’s numerical or non-numerical. But the SUM() and AVG() column only works for columns containing numerical values.

Syntax

The syntax of each of these functions is written below. It has been illustrated in a query with SQL SELECT Statement and WHERE Clause.

COUNT() Syntax

SELECT COUNT(any_column)
FROM table_name
WHERE condition;

SUM() Syntax

SELECT AVG(any_numerical_column)
FROM table_name
WHERE condition;

AVG() Syntax

SELECT AVG(any_numerical_column)
FROM table_name
WHERE condition;

Important Note. Any of these functions do not take NULL value into consideration. If there are 10 rows for a table column and out of if three is null, then only the 7 rows with valid column values will be taken into consideration. The count for such a case will be 7, the sum and the average will also be only calculated for those 7 values.

Using the WHERE clause is optional and depends upon your requirement.

Examples

Example for each of the COUNT(), SUM() and AVG() function is given below and also a combined example is illustrated with the use of AS keyword at the last sub-section. In our demo database, we’ll run the example queries on a table named fee_amounts.

The screenshot given below gives you a quick overview of this demonstration database table.

SQL COUNT AVG MAX DEMO Database

You can clearly see the column named packed_structure has some NULL column values as well.

SQL COUNT() Examples

The following query tells the count of the column packed_structure.

SQL COUNT() Function Example

Although there are 9 total records in the table. As the column packed_structure has a null value for 5 of the records, therefore the COUNT() functional evaluates the count of the column values to be 4.

You can also apply the count function for all of the columns at the same time using the * symbol. The following query will get the count for the entire table records.

SQL COUNT OF ALL RECORDS EXAMPLE

Basically, it will return the count of the column with maximum records.

SQL SUM() Example

The following query will find out the SUM of the column fee_max_amount.

SQL SUM Function Example

SQL AVG() Example

The SQL query written below will evaluate the average for the column fee_max_amount.

SQL AVG() FUNCTION Example

Combined Example With AS Keyword Usage

In the following query, we’re using all these three functions, COUNT(), SUM() and AVG() and fetching the results as our own defined column names using the AS keyword.

SQL COUNT SUM AVG Combined Example

I hope you find this guide useful. If so, do share it with others who are willing to learn SQL. If you have any questions related to this article, feel free to ask them in the comments section.

Helpful Associated Guide.

Also, don’t forget to Subscribe to WTMatter!

Subscribe To WTMatter!

Receive updates of our latest articles via email. Enter your email address below to get started.

Leave a Reply

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