MariaDB Crash Course - Ben Forta [35]
Input
SELECT MIN(prod_price) AS min_price
FROM products;
Output
+-----------+
| min_price |
+-----------+
| 2.50 |
+-----------+
Analysis
Here MIN() returns the price of the least expensive item in the products table.
* * *
Tip: Using MIN() with Non-Numeric Data
As with the MAX() function, MariaDB allows MIN() to be used to return the lowest value in any columns including textual columns. When used with textual data, MIN() returns the row that would be first if the data were sorted by that column.
* * *
* * *
Note: NULL Values
Column rows with NULL values in them are ignored by the MIN() function.
* * *
The SUM() Function
SUM() is used to return the sum (total) of the values in a specific column.
Here is an example to demonstrate this. The orderitems table contains the actual items in an order, and each item has an associated quantity. The total number of items ordered (the sum of all the quantity values) can be retrieved as follows:
Input
SELECT SUM(quantity) AS items_ordered
FROM orderitems
WHERE order_num = 20005;
Output
+---------------+
| items_ordered |
+---------------+
| 19 |
+---------------+
Analysis
The function SUM(quantity) returns the sum of all the item quantities in an order, and the WHERE clause ensures that just the right order items are included.
SUM() can also be used to total calculated values. In this next example the total order amount is retrieved by totaling item_price*quantity for each item:
Input
SELECT SUM(item_price*quantity) AS total_price
FROM orderitems
WHERE order_num = 20005;
Output
+-------------+
| total_price |
+-------------+
| 149.87 |
+-------------+
Analysis
The function SUM(item_price*quantity) returns the sum of all the expanded prices in an order, and again the WHERE clause ensures that just the correct order items are included.
* * *
Tip: Performing Calculations on Multiple Columns
All the aggregate functions can be used to perform calculations on multiple columns using the standard mathematical operators, as shown in the example.
* * *
* * *
Note: NULL Values
Column rows with NULL values in them are ignored by the SUM() function.
* * *
Aggregates on Distinct Values
The five aggregate functions can all be used in two ways:
• To perform calculations on all rows, specify the ALL argument, or specify no argument at all (because ALL is the default behavior).
• To only include unique values, specify the DISTINCT argument.
* * *
Tip: ALL Is Default
The ALL argument need not be specified because it is the default behavior. If DISTINCT is not specified, ALL is assumed.
* * *
The following example uses the AVG() function to return the average product price offered by a specific vendor. It is the same SELECT statement used in the previous example, but here the DISTINCT argument is used so the average only takes into account unique prices:
Input
SELECT AVG(DISTINCT prod_price) AS avg_price
FROM products
WHERE vend_id = 1003;
Output
+-----------+
| avg_price |
+-----------+
| 15.998000 |
+-----------+
Analysis
As you can see, in this example avg_price is higher when DISTINCT is used because there are multiple items with the same lower price. Excluding them raises the average price.
* * *
Caution: Using DISTINCT With COUNT()
DISTINCT may only be used with COUNT() if a column name is specified. DISTINCT may not be used with COUNT(*), and so COUNT(DISTINCT *) is not allowed and generates an error. Similarly, DISTINCT must be used with a column name and not with a calculation or expression.
* * *
* * *
Tip: Using DISTINCT with MIN() and MAX()
Although DISTINCT can technically be used with MIN() and MAX(), there is actually no value in doing so. The minimum and maximum values in a column are the same whether or not only distinct values are included.
* * *
Combining Aggregate Functions
All