MySQL Aggregate Functions

1). MySQL COUNT function returns the number of records in a select query and allows you to count all rows in a table or rows that match a particular condition.

Ex: SELECT COUNT(*) AS "Total Rows" FROM employees;

2).The IN operator allows you to specify multiple values in a WHERE clause.

Ex: SELECT column_name(s) FROM table_name WHERE column_name IN (value1, value2, ...);

3).The MySQL SUM function returns the summed value of an expression.

Ex: SELECT SUM(column_name) FROM table_name WHERE condition;

4).The MySQL MIN function returns the minimum value in a set of values.

Ex: SELECT MIN(column_name) FROM table_name WHERE condition;

5).The MySQL MAX function returns the maximum value in a set of values.

Ex: SELECT MAX(column_name) FROM table_name WHERE condition;

6).The MySQL HAVING clause is used in the SELECT statement to specify filter conditions for a group of rows or aggregates.
Ex: Now suppose based on the above table you want to count the number of customers in each country. Only include countries with more than 1 customers, then you can do it as follows:

SELECT COUNT(CustomerID) AS "Number of customers", Country FROM customers GROUP BY Country HAVING COUNT(CustomerID) > 1;

7).The MYSQL GROUP BY Clause is used to collect data from multiple records and group the result by one or more column.
The GROUP BY statement is often used with aggregate functions such as SUM, AVG, MAX, MIN and COUNT. It is generally used in a SELECT statement.

Ex: SELECT COUNT(CustomerID) AS "Number of customers", Country FROM customers GROUP BY Country;

8).The MySQL BETWEEN operator selects values within a given range in a SELECT, INSERT, UPDATE, and DELETE statement. The values can be numbers, text, or dates.

Ex: SELECT * FROM books WHERE Price BETWEEN 10 AND 25;

9).MySQL DISTINCT with multiple columns:
When querying data from a table, you may get duplicate rows. In order to remove these duplicate rows, you use the DISTINCT clause in the SELECT statement.

Ex: SELECT DISTINCT state, city FROM customers WHERE state IS NOT NULL ORDER BY state, city;