MariaDB Crash Course - Ben Forta [34]
* * *
Note: Standard Deviation
A series of standard deviation aggregate functions are also supported by MariaDB but are not covered in the chapters.
* * *
The AVG() Function
AVG() is used to return the average value of a specific column by counting both the number of rows in the table and the sum of their values. AVG() can be used to return the average value of all columns or of specific columns or rows.
This first example uses AVG() to return the average price of all the products in the products table:
Input
SELECT AVG(prod_price) AS avg_price
FROM products;
Output
+-----------+
| avg_price |
+-----------+
| 16.133571 |
+-----------+
Analysis
The previous SELECT statement returns a single value, avg_price, that contains the average price of all products in the products table. avg_price is an alias as explained in Chapter 10, “Creating Calculated Fields.”
AVG() can also be used to determine the average value of specific columns or rows. The following example returns the average price of products offered by a specific vendor:
Input
SELECT AVG(prod_price) AS avg_price
FROM products
WHERE vend_id = 1003;
Output
+-----------+
| avg_price |
+-----------+
| 13.212857 |
+-----------+
Analysis
This SELECT statement differs from the previous one only in that this one contains a WHERE clause. The WHERE clause filters only products with a vend_id of 1003, and, therefore, the value returned in avg_price is the average of just that vendor’s products.
* * *
Caution: Individual Columns Only
AVG() may only be used to determine the average of a specific numeric column, and that column name must be specified as the function parameter. To obtain the average value of multiple columns, multiple AVG() functions must be used.
* * *
* * *
Note: NULL Values
Column rows containing NULL values are ignored by the AVG() function.
* * *
The COUNT() Function
COUNT() does just that: It counts. Using COUNT(), you can determine the number of rows in a table or the number of rows that match a specific criterion.
COUNT() can be used two ways:
• Use COUNT(*) to count the number of rows in a table, whether columns contain values or NULL values.
• Use COUNT(column) to count the number of rows that have values in a specific column, ignoring NULL values.
This first example returns the total number of customers in the customers table:
Input
SELECT COUNT(*) AS num_cust
FROM customers;
Output
+----------+
| num_cust |
+----------+
| 5 |
+----------+
Analysis
In this example, COUNT(*) is used to count all rows, regardless of values. The count is returned in num_cust.
The following example counts just the customers with an e-mail address:
Input
SELECT COUNT(cust_email) AS num_cust
FROM customers;
Output
+----------+
| num_cust |
+----------+
| 3 |
+----------+
Analysis
This SELECT statement uses COUNT(cust_email) to count only rows with a value in the cust_email column. In this example, cust_email is 3 (meaning that only three of the five customers have e-mail addresses).
* * *
Note: NULL Values
Column rows with NULL values in them are ignored by the COUNT() function if a column name is specified, but not if the asterisk (*) is used.
* * *
The MAX() Function
MAX() returns the highest value in a specified column. MAX() requires that the column name be specified, as seen here:
Input
SELECT MAX(prod_price) AS max_price
FROM products;
Output
+-----------+
| max_price |
+-----------+
| 55.00 |
+-----------+
Analysis
Here MAX() returns the price of the most expensive.
* * *
Tip: Using MAX() with Non-Numeric Data
Although MAX() is usually used to find the highest numeric or date values, MariaDB allows it to be used to return the highest value in any column including textual columns. When used with textual data, MAX() returns the row that would be the last if the data were sorted by that column.
* * *
* * *
Note: NULL Values
Column rows with NULL values in them are ignored by the MAX() function.
* * *
The MIN() Function
MIN()