MySQL must know-12th Chapter-Summary data

12th Chapter Summary Data

This chapter describes what are the aggregate functions of SQL and how to use them to summarize the data of a table.

12.1 Aggregation Functions

We often need to summarize the data without actually retrieving it, and this provides a special function for MySQL. Using these functions, a MySQL query can be used to retrieve data for analysis and report generation. There are several examples of this type of retrieval.

• Determines the number of rows in a table (or the number of rows that satisfy a condition or contain a particular value).
• Gets the and of the rows group in the table.
• Find the maximum, minimum, and average values for a table column (or all rows or some particular row).
  The above examples all need to summarize the data in the table (not the actual data itself). Therefore, returning the actual table data is a waste of time and processing resources (let alone bandwidth). Again, what you actually want is a summary of the information. To facilitate this type of retrieval, MySQL gives 5 aggregation functions, as shown in table 12-1. These functions are able to perform the search listed above. A function that aggregates functions (aggregate function) runs on a row group and evaluates and returns a single value.
  

The use of each function is described below.
Standard deviation MySQL also supports a range of standard deviation aggregation functions, but this is not covered in this book.

12.1.1 AVG () function

AVG () calculates the average of the column by counting the number of rows in the table and calculating the sum of the specified column values. AVG () can be used to return the average of all columns, or it can be used to return the average of a particular column or row. The following example uses AVG () to return the average price for all products in the product table:

This SELECT statement returns the value Avg_price, which contains the average price for all products in the product table. As described in chapter 10th, Avg_price is an alias. AVG () can also be used to determine the average of a particular column or row. The following example returns the average price of a product provided by a specific vendor:

This SELECT statement differs from the previous one in that it contains a WHERE clause. This WHERE clause only filters out products with a vend_id of 1003, so the value returned in Avg_price is only the average of the product for that vendor.
Only a single column AVG () can only be used to determine the average value of a particular numeric column, and the column name must be given as a function parameter. To get the average of multiple columns, you must use more than one AVG () function.
The null value of the AVG () function ignores rows where the column value is null.

12.1.2 COUNT () function

The count () function is counted. Count () can be used to determine the number of rows in a table or the number of lines that meet a specific condition. The COUNT () function is used in two ways.

• Use COUNT (*) to count the number of rows in a table, regardless of whether the table column contains a null value (NULL) or a non-null value.
• Use Count (column) to count the rows that have values in a particular column, ignoring null values. The following example returns the total number of customers in the Customers table:
  

In this example, all rows are counted with count (*), regardless of the value of the columns in the row. The count value is returned in Num_cust.
The following example only counts customers with e-mail addresses:

This SELECT statement uses count (Cust_email) to count the rows that have values in the Cust_email column. In this example, the count of Cust_email is 3 (indicating that only 3 of the 5 customers have e-mail addresses).
Null value if you specify a column name, rows with a null value for the specified column are ignored by the count () function, but are not ignored if the count () function uses an asterisk (*).

12.1.3 MAX () function

Max () returns the maximum value in the specified column. MAX () requires that the column name be specified as follows:

Here, MAX () returns the price of the most expensive item in the Products table.
Use Max () for non-numeric data although Max () is typically used to find the largest numeric or date value, MySQL allows it to return the maximum value in any column, including the maximum value in a text column. When used with text data, Max () returns the last row if the data is sorted by the appropriate column.
The null value of the MAX () function ignores rows where the column value is null.

12.1.4 MIN () function

MIN () functions just as opposed to the max () function, which returns the minimum value of the specified column. As with Max (), MIN () requires that the column name be specified as follows:

where min () returns the price of the cheapest item in the Products table.
Using the min () min () function for non-numeric data is similar to the max () function, which MySQL allows to return the smallest value in any column, including the smallest value in the returned text column. When used with text data, MIN () returns the front row if the data is sorted by the appropriate column.
The null value MIN () function ignores rows where the column value is null.

12.1.5 SUM () function

SUM () returns the sum (grand total) of the specified column value. As an example, the OrderItems table contains the actual items in the order, and each item has a corresponding quantity (quantity). The total number of items ordered (sum of all quantity values) can be retrieved as follows:

The function sum (quantity) returns the sum of the quantities of all items in the order, and the WHERE clause guarantees that only the items in an item's order are counted. SUM () can also be used to aggregate calculated values. In the following example, the total order amount is calculated by summing up the item_price*quantity of each item:

The function sum (item_price*quantity) returns the sum of the prices of all items in the order, and the WHERE clause guarantees that only the items in an item's order are counted.
Perform calculations on multiple columns as shown in this example, all aggregation functions can be used to perform calculations on multiple columns, using standard arithmetic operators.
The null value of the SUM () function ignores rows where the column value is null.

12.2 Aggregating different values

The use of the distinct of the aggregation functions to be described below in MySQL 5 and later versions has been added to MySQL 5.0.3. The contents described below do not work correctly in MySQL 4.x. The above 5 aggregation functions can be used as follows:

• Performs a calculation on all rows, specifying the all parameter or not giving the parameter (because all is the default behavior);
• Contains only a different value, specifying the DISTINCT parameter.
  All for the default all parameter does not need to be specified because it is the default behavior. If you do not specify distinct, all is assumed.
  The following example uses the AVG () function to return the average price of a product provided by a particular vendor. It is the same as the SELECT statement above, but uses the distinct parameter, so the average value only takes into account the various prices:
  

As you can see, after using distinct, the avg_price in this example is relatively high because there are multiple items with the same lower price. Excluding them increases the average price. Note If you specify a column name, distinct can only be used for count (). DISTINCT cannot be used with count (*), so count (DISTINCT) is not allowed, otherwise an error is generated. Similarly, distinct must use a column name and cannot be used for calculations or expressions.
Using distinct for min () and Max () although distinct is technically available for min () and Max (), this does not actually have value. The minimum and maximum values in a column are the same regardless of whether they contain different values.

12.3 Combined aggregation functions

All the aggregate function examples so far have only involved a single function. However, the SELECT statement can actually contain multiple aggregate functions as needed. Take a look at the following example:

Here, 4 aggregation calculations are performed with a single SELECT statement, returning 4 values (the number of items in the Products table, the highest, lowest, and average of the product price).
Aliases should not use the actual column names in the table when specifying aliases to contain the results of a clustered function. While this is not illegal, using a unique name makes your SQL easier to understand and use (and will be easy to troubleshoot in the future).

12.4 Summary

Aggregation functions are used to summarize data. MySQL supports a series of aggregation functions that can be used in several ways to return the desired results. These functions are designed efficiently, and their return results are generally much faster than you would calculate in your own client application.

MySQL must know-12th Chapter-Summary data