T-SQL: Graduates need to know series (ix)

"SQL must know" reading notes--9th lesson summary data

9.1 Aggregation Function: A function that runs on some rows, evaluates and returns a value
Case:
--Determine the function in the table
--Get some rows in the table and
--Find out the maximum, minimum, and average values of table columns

-Save time and bandwidth when we actually want to aggregate information instead of needing the data itself.

--Table 9-1
--Function description
--avg () returns the average of a column
--count () returns the number of rows in a column
--max () returns the maximum value of a column
--min () returns the minimum value of a column
--sum () returns the sum of a column value

9.1.1AVG () Function: Returns the average of a column

1 SELECT AVG  as  2 from dbo. Products; 4 -- Analysis: This avg_price contains the average price for all products in the product table

SELECT AVG  as   from dbo. Products WHERE='DLL01'; -- Analysis: WHERE filters out products with vend_id as DLL01, so the value returned by Avg_price is only the average of the vendor's products

"Attention" The AVG () function ignores rows where the column value is NULL

9.1.2 COUNT () function: Determines the number of rows in a table or the number of lines that match a specific condition
--How to use:
--Techniques for the number of rows in a table using COUNT (*), regardless of whether the table column contains a null value (NULL) or a non-null value
--count the rows with values in a particular column using Count (column), ignoring NULL values

SELECT COUNT (* as num_cust from dbo.) Customers; -- returns the number of customers in the Customers table

SELECT COUNT  as Num_cust  from dbo. Customers; -- only customers with e-mail addresses are counted here, and the result is 3, indicating that only 3 of the 5 customers have e-mail -- Parse: Because the column name is specified here, COUNT () ignores rows that have a null value for the specified column

9.1.3 Max () function: Returns the maximum value in the specified column
--Requirements: Specify column names

SELECT MAX  as Max_price  from dbo. Products; -- returns the price of the most valuable item in the Products table

　　"Attention" MAX () ignores rows with null column values

9.1.4 min () function: Returns the minimum value of the specified column, in contrast to MAX ().
--Requirements: Specify column names

SELECT MIN  as Min_price  from dbo. Products; -- The price of the cheapest item in the Products table is returned .

　　"Attention" MIN () ignores rows with NULL column values

9.1.5 sum () function: Returns the sum of the specified column values (total)

SELECT SUM  as items_ordered  from dbo. OrderItemsWHERE=20005; -- Analysis: SUM (quantity) returns the sum of the quantities of all items in the order, where clause guarantees that only items in an item's order are counted

SELECT SUM (Item_price* as total_price from dbo.) OrderItemsWHERE=20005; -- Analysis: SUM (item_price*quantity) returns the price of all items in the order and the WHERE clause guarantees that the items in an item's order are counted

"Attention" SUM () ignores rows where the column value is NULL

9.2 Aggregation of different values
--The above 5 aggregation functions can be used: DISTINCT parameter, contains only a different value

SELECT AVG (DISTINCT as from dbo.) Products WHERE='DLL01'; -- because the DISTINCT parameter is used, the average value only takes into account the various prices

　　"Attention" DISTINCT cannot be used for count (*), but can be used for count (column)
　　the hint is available for MIN () and MAX (), but it has no real value

9.3 combined aggregate function

SELECT COUNT (*  as Num_items,    MIN as price_min,    MAX  as Price_max,     AVG  as Price_avg  from dbo. Products; -- 4 aggregation calculations were performed using a single SELECT statement, returning 4 values (number of items in the Products table, highest value of product price, lowest value, and average) -- use aggregation functions to suggest aliases to make your data clearer

T-SQL: Graduates need to know series (ix)