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'` `;` |