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.
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.
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
The screenshot given below gives you a quick overview of this demonstration database table.
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
SELECT COUNT(packed_structure) FROM fee_amounts
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.
SELECT COUNT(*) FROM fee_amounts
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
SELECT SUM(fee_max_amount) FROM fee_amounts
SQL AVG() Example
The SQL query written below will evaluate the average for the column
SELECT AVG(fee_max_amount) FROM fee_amounts
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.
SELECT COUNT(*) AS COUNT, SUM(fee_max_amount) AS TOTAL, AVG(fee_max_amount) AS AVERAGE FROM fee_amounts
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.
- PHP MySQL CRUD Tutorial For Beginners
- Rename A Column In MySQL
- Import SQL File (MySQL Command Line & GUI)
Also, don’t forget to Subscribe to WTMatter!