COUNT() function returns the number of rows that matches a specified criterion. MySQL provides us with aggregate functions like
AVG() for these purposes.
COUNT()is used to count the number of rows for a given condition.
COUNT()works on numeric as well as non-numeric values.
SUM()is used to calculate the total sum of all values in the specified numeric column.
AVG()is used to calculate the average value of the specified numeric column.
Note that, all three functions ignore NULL values.
The COUNT function returns the total number of values in the specified field. It works on both numeric and non-numeric data types. All aggregate functions by default exclude nulls values before working on the data. COUNT (*) is a special implementation of the COUNT function that returns the count of all the rows in a specified table. COUNT (*) also considers Nulls and duplicates.
MySQL AVG function returns the average of the values in a specified column. Just like the SUM function, it works only on numeric data types.
SUM() function returns the total sum of a numeric column.
the MySQL SUM function which returns the sum of all the values in the specified column. SUM works on numeric fields only. Null values are excluded from the result returned.
Below is a selection from the “Products” table in the Northwind sample database:
|1||Chais||1||1||10 boxes x 20 bags||18|
|2||Chang||1||1||24 – 12 oz bottles||19|
|3||Aniseed Syrup||1||2||12 – 550 ml bottles||10|
|4||Chef Anton’s Cajun Seasoning||2||2||48 – 6 oz jars||22|
|5||Chef Anton’s Gumbo Mix||2||2||36 boxes||21.35|
The following SQL statement finds the number of products:
Note: NULL values are not counted.
The following SQL statement finds the average price of all products:
Note: NULL values are ignored.
Below is a selection from the “OrderDetails” table in the Northwind sample database:
The following SQL statement finds the sum of the “Quantity” fields in the “OrderDetails” table:
More Examples of MySQL COUNT()
Consider the below ConferenceGuests table.
1. Simple example using COUNT()
Let us begin with a simple example demonstrating the
COUNT() function. Let us count the number of records in the above table. We do so using the SELECT query as shown below:
And we get the output as,
If we look closely at the ConferenceGuests table, we see that the record for guest with ID – 9 is missing. Hence, there are 15 records.
2. Counting Unique Values In A Column
Let us count the unique values in the Country column. For this, we will use the DISTINCT Keyword. We use the following query,
COUNT() function only counts the unique values in the Country column and returns the output as follows,
3. Using COUNT() With the WHERE Clause
Let us now use
COUNT() and specify a condition using the WHERE clause. How many unique states from India are represented in the conference?
The output is as follows,Learn more
4. Using COUNT() With the LIKE Clause
Let us take a more complex example. Let us use the
LIKE clause with the
COUNT() function. How about finding the number of guests whose name begins with an ‘A’. The query for that is:
The output is as follows,
More Examples of MySQL SUM()
Consider the below Employee table.
1. Simple Example of SUM()
Let us calculate the sum of the Salary column. We do so using the query,
The sum of the Salary column is returned in the output below,Learn more
2. Using SUM() With the WHERE Clause
How about finding the sum of salaries of all employees in the Operations department?
We get the following output,
3. Using SUM() with the IN Operator
Let us take a little complex example now. How about finding the salaries of employees who have ‘MUM’ and ‘PUN’ as their Office_Code values? We will take the help of the IN Operator and the query is as follows:
We get the following output,
More Examples of MySQL AVG()
1. Simple Example of AVG()
Let us find the average salary of all employees in the company. We use the below query:
And we get the output as:
As you can see, we get the average value of the Salary column.
2. Using AVG() with the WHERE Clause
AVG() can be used with different clauses. Here, we will demonstrate how we use it with the
WHERE clause. How about finding out the average salary in the Executive department?