SQL is required and SQL is required

SQL is required and SQL is required
Aggregate functions

1. AVG () function

Input:SELECT AVG (Prod_price) ASAvg_priceFROMProducts

Output:

Warning: only used for a single column
AVG () can only be used to determine the average value of a specific value column, and the column name must be given as a function parameter. To obtain the average value of multiple columns, you must use multiple AVG () functions.
Description: NULL value.
The AVG () function ignores rows whose column value is NULL.

2.COUNT ()Function

Input:SELECT COUNT (*) ASNum_custFROMMERs

Output:

Description: NULL value.
If a column name is specified, the COUNT () function ignores the rows whose values are null. However, if the COUNT () function uses an asterisk (*), the column name is not ignored.

3.MAX ()Function

MAX () returns the maximum value in the specified column.

Input:SELECT MAX (Prod_price) ASMax_priceFROMProducts

Output:

Tip: Use MAX () for non-numeric data ()
Although MAX () is generally used to find the maximum value or date value, many (not all) DBMS allow it to return the maximum value in any column, including the maximum value in the returned text column. When used for text data, MAX () returns the last row sorted by this column.
Description: NULL value.
The MAX () function ignores rows whose column value is NULL.

4. MIN () function

The MIN () function is opposite to the MAX () function. It returns the minimum value of the specified column.

Omitted

5.SUM ()Function

SUM () is used to return the SUM (total) of the specified column value ).

Omitted

Aggregate functions

Input:

Select count (*) ASNum_items,
MIN (Prod_price)Price_min,
MAX (Prod_price)Price_max,
AVG (Prod_price)Price_avg
FROMProducts;

Output:

GROUP DATA

1. Data grouping

Input:

Select count (*)Num_prods
FROMProducts
WHEREVend_id = 'dl01 ';

Output:

2. Create a group

Use group.

Input:

SELECTVend_id,COUNT (*) ASNum_prods
FROMProducts
GROUPVend_id;

Output:

Before using the group by clause, you need to know some important provisions..

• The group by clause can contain any number of columns. Therefore, groups can be nested to GROUP data in more detail.
• If a GROUP is nested in the group by clause, the data is summarized on the last specified GROUP. In other words, when a group is created, all specified columns are calculated together (so data cannot be retrieved from individual columns ).
• Each column listed in the group by clause must be a search column or a valid expression (but not a clustering function ). If an expression is used in SELECT, the same expression must be specified in the group by clause. Aliases cannot be used.
• Most SQL implementations do not allow a GROUP BY column to have a variable-length data type (such as text or remarks fields ).
• Except for the aggregate calculation statement, each column in the SELECT statement must be given in the group by clause.
• If the group column contains rows with NULL values, NULL is returned as a group. If the column contains multiple NULL values, they are divided into one group.
• The group by clause must appear after the WHERE clause and before the order by clause.
  

3. filter groups

Input:

SELECTCust_id,COUNT (*)Orders
FROMOrders
GROUPCust_id
Having count (*)> = 2;

Output:

The first three rows of this SELECT statement are similar to the preceding statements. The HAVING clause is added to the last row, which filters those groups whose COUNT (*)> = 2 (more than two orders.

For a better understanding, let's look at the following example. It lists suppliers with more than two products whose prices are greater than or equal to 4:

4. Grouping and sorting

Group by and order by often perform the same job, but they are very different. It is important to understand this. Table 10-1 summarizes the differences between them.

Instance:

Input:

SELECTOrder_num,COUNT (*)Items
FROMOrderItems
GROUPOrder_num
Having count (*)> = 3;

Output:

Comparison:

Input:

SELECTOrder_num,COUNT (*)Items
FROMOrderItems
GROUPOrder_num
Having count (*)> = 3
ORDERItems, order_num;

Output:

In this example, the group by clause is used to GROUP data BY Order Number (order_num column) so that the COUNT (*) function can return the number of items in each order. The HAVING clause filters data so that only orders containing three or more items are returned. Finally, sort the output BY the order by clause.

Author: Jin Xiao
Source: http://www.cnblogs.com/jinxiao-pu/p/6824666.html
The copyright of this article is shared by the author and the blog. You are welcome to repost this article, but you must keep this statement without the author's consent and provide a connection to the original article on the article page.

If you think it is good, click a recommendation!