COUNT()
The COUNT() function is used to count the number of occurrences of all non-null values in a column.
For example
SELECT COUNT(C.First_Name)
FROM Customers as C, Orders as O
WHERE C.Customer_ID = O.Customer_ID
This statement would return the number of rows that had a non-null value in the First_Name column of
the Customers table that matched the WHERE clause criteria.
Another use of the COUNT() function is to use it in the following way:
SELECT COUNT(*) as Num
FROM Customers as C, Orders as O
WHERE C.Customer_ID = O.Customer_ID
This statement would return the total number of rows. This is especially useful when you need to find
out how many rows you have in a table.
COUNT(DISTINCT)
The COUNT(DISTINCT) function is used to count the unique non-null occurrences of a column or
columns. For example
SELECT COUNT(DISTINCT First_Name,)
FROM Customers
This statement would return the number of rows of different first names. For example, there are 10 rows
of data in the Customers table. The first three rows have Taylor in the First_Name column. The next
2 rows have Sydney, the next has Jill, the last 4 rows have Trisha. The previous statement would
return the number 4. That is the total number of unique names that are stored in the First_Name
column.
The COUNT and COUNT(DISTINCT) functions can be used on any data type. The rest of the functions
must be used on numeric data types.
MAX()
The MAX() function returns the highest value in the column. This function is handy when you are
creating your own sequence numbers. The syntax looks like the following:
SELECT MAX(Customer_ID)
FROM Customers
This statement would return the highest value in the table.
MIN()
The MIN() function returns the lowest value in a column. The syntax for the MIN() function looks like
the following:
SELECT MIN(Customer_ID)
FROM Customers
AVG()
The AVG() function returns the average value of the designated column. The function would be used as
follows:
SELECT AVG(Price)
FROM Orders
This statement would return the average price of all the prices in the Orders table. MySQL performs all
the calculations for you.
Note Remember that a NULL value is really no value at all. So, when you SUM or AVG a
column that contains NULL values, the NULL values will not affect your results in
any way.SUM()
This function totals the values in a given column. The function is used as follows:
SELECT SUM(Price)
FROM Orders