The COUNT() function returns the number of rows that matches a specified criterion. MySQL provides us with aggregate functions like COUNT(), SUM() and 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.
COUNT() Syntax
SELECT COUNT(column_name)
FROM table_name
WHERE condition;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.
AVG() Syntax
SELECT AVG(column_name)
FROM table_name
WHERE condition;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.
The SUM() function returns the total sum of a numeric column.
SUM() Syntax
SELECT SUM(column_name)
FROM table_name
WHERE condition;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.
Demo Database
Below is a selection from the “Products” table in the Northwind sample database:
| ProductID | ProductName | SupplierID | CategoryID | Unit | Price |
|---|---|---|---|---|---|
| 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 |
COUNT() Example
The following SQL statement finds the number of products:
Example
SELECT COUNT(ProductID)
FROM Products;Note: NULL values are not counted.
AVG() Example
The following SQL statement finds the average price of all products:
Example
SELECT AVG(Price)
FROM Products;Note: NULL values are ignored.
Demo Database
Below is a selection from the “OrderDetails” table in the Northwind sample database:
| OrderDetailID | OrderID | ProductID | Quantity |
|---|---|---|---|
| 1 | 10248 | 11 | 12 |
| 2 | 10248 | 42 | 10 |
| 3 | 10248 | 72 | 5 |
| 4 | 10249 | 14 | 9 |
| 5 | 10249 | 51 | 40 |
SUM() Example
The following SQL statement finds the sum of the “Quantity” fields in the “OrderDetails” table:
Example
SELECT SUM(Quantity)
FROM OrderDetails;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:
| 1 | SELECT COUNT(*) FROM ConferenceGuests; |
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,
| 1 | SELECT COUNT(DISTINCT Country) FROM ConferenceGuests; |
The 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?
| 1 | SELECT COUNT(DISTINCT State) FROM ConferenceGuests WHERE Country='India'; |
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:
| 1 | SELECT COUNT(Name) FROM ConferenceGuests WHERE Name LIKE 'A%'; |
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,
| 1 | SELECT SUM(Salary) FROM Employee; |
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?
| 1 | SELECT SUM(Salary) FROM Employee WHERE Department='Operations'; |
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:
| 1 | SELECT SUM(Salary) FROM Employee WHERE Office_Code IN ('MUM', 'PUN'); |
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:
| 1 | SELECT AVG(Salary) FROM Employee; |
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
Just like COUNT() and SUM(), 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?
| 1 | SELECT AVG(Salary) FROM Employee WHERE Department='Executive'; |
