"SQL. Basic Build-section III (3/4)"

--Tips: Aggregation and sequencing


--One, the aggregation query to the table

--1. Aggregation function

--(1) 5 commonly used functions:

--①count: Calculates the number of records (rows) in the table.

--②sum: Calculates the data aggregate value of a numeric column in a table.

--③avg: Calculates the data average of a numeric column in a table.

--④max: The maximum value of the data in any column in the table is calculated.

--⑤min: The minimum value of the data in any column in the table is calculated.



--(2) Aggregation: summarizes multiple rows into one row.

--2. Calculating the number of rows of data in a table

--Example
SELECT COUNT (*)--*: parameter, which represents all columns
FROM dbo. Conbio;

--------------------------------------

--3. Calculating the number of rows of data other than NULL

--Change the parameter of COUNT (*) to the column of the specified object, and you can get the number of non-NULL rows for that column.

SELECT COUNT (CONBIO_PRICE2)
FROM dbo. Conbio;

--"Remarks" except for the COUNT function, other functions cannot use asterisks as arguments.

--The results of the "remarks" COUNT function vary depending on the parameters. COUNT (*) Gets the number of data rows that contain null, and COUNT (< column name >) gets the number of rows of data other than null.

--------------------------------------

--4. Calculating Total values

Select
SUM (Conbio_price1) as Sum_conbio_price1,--sum
AVG (Conbio_price1) as Avg_conbio_price1,--average
Max (Conbio_price1) as Max_conbio_price1,--Max
Min (Conbio_price1) as Min_conbio_price1--minimum value
FROM dbo. Conbio;

--"Remarks" all aggregate functions, if the column name is a parameter, ignores the line where the NULL value is located.
------------------

SELECT MAX (conbio_date),--conbio_date as Date
MIN (Conbio_date)
FROM dbo. Conbio

--the "remarks" max/min function works almost for columns of all data types. The SUM/AVG function applies only to columns of numeric types.

--------------------------------------

--5. Remove duplicate values using aggregate functions (keyword DISTINCT)

--Example 1: Calculate the number of data rows after removing duplicate data

SELECT COUNT (DISTINCT conbio_varieties)
From Dbo.conbio;

------------------

--Example 2: Calculate the number of rows of data before deleting the result of duplicate data

SELECT DISTINCT COUNT (conbio_varieties)
FROM dbo. Conbio;

---"remarks" You can delete duplicate data by using DISTINCT (example 1) in the parameters of the aggregate function. DISTINCT is not limited to the COUNT function, all aggregate functions can be used.

--------------------------------------

--second, grouping the tables

--1.GROUP BY clause

--Syntax:
--select < column name 1>, < column name 2>,...
--from < table name >
--group by < column name 1>, < column name 2>,...;

--Example
SELECT conbio_varieties as ' product category ',
COUNT (*) as ' quantity '
From Dbo.conbio
GROUP by Conbio_varieties;

--The column specified in the remarks GROUP by clause is called an aggregate key or a grouping column.

--"Write Order of Clauses (tentative)" SELECT----------

------------------

--2. A condition in which the aggregation key contains NULL

SELECT Conbio_price2, COUNT (*)
From Dbo.conbio
GROUP by Conbio_price2;

--When a "memo" aggregation key contains null, it is also represented as a null line in the result.

--------------------------------------

Impact of--3.where on GROUP by execution results

--Grammar
--select < column name 1>, < column name 2>,...
--from < table name >
--where < expressions >
--group by < column name 1>, < column name 2>,...

SELECT Conbio_price2, COUNT (*)
From Dbo.conbio
WHERE conbio_varieties = ' clothes '
GROUP by Conbio_price2

--This is filtered first based on the conditions specified by the WHERE clause, and then the aggregation process.

--"Execution order" from---------and GROUP by---select. Here is the order of execution, which is different from the previous writing order.

--------------------------------------

--4. Common errors related to aggregate functions and GROUP by clauses

--(1) Error-prone: Extra columns are written in the SELECT clause

The SELECT clause only has the following three elements:

--① constant

--② aggregation function

The column name (that is, the aggregation key) specified in the--③group by clause

--Easy wrong point 1

--"Summary" when using the GROUP by clause, the SELECT clause cannot have a column name other than the aggregation key.

--(2) Easy error: Alias of column in GROUP by clause

--Review the order of execution that was previously said, and the SELECT clause is executed after the GROUP by clause. Therefore, the alias is not recognized when executing to the GROUP by clause.

--the "summary" GROUP BY clause cannot use the alias defined in the SELECT clause.


--(3) Easy error: Can the results of the GROUP by clause be sorted?

--"Answer" it is random. If you want to sort, use the ORDER BY clause.

--The display of the results of the "summarize" GROUP by clause is unordered.


--(4) Error-prone: Using aggregate functions in the WHERE clause
--"Summary" You can use aggregate functions only in the SELECT clause and the HAVING clause (and the ORDER by clause).

--------------------------------------

--Iii. specifying conditions for aggregation results

--1.HAVING clause

-The WHERE clause intelligently specifies the condition of the record (row), and cannot be used to specify the condition of the group.

The "remark" has the present participle (owning).

--Syntax:
--select < column name 1>, < column name 2>,...
--from < table name >
--group by < column name 1>, < column name 2>,...
--having < group results corresponding conditions >

--"Writing order" select---------

SELECT conbio_varieties, COUNT (*)
From Dbo.conbio
GROUP by Conbio_varieties
Having COUNT (*) = 2

------------------

The constituent elements of the--2.having clause

--(1) 3 elements:

--① constant

--② aggregation function

The column name (that is, the aggregation key) specified in the--③group by clause

------------------

--3.having and WHERE

-some conditions can be written in the HAVING clause and can be written in the WHERE clause. These conditions are the conditions that the aggregation key corresponds to.

--"recommended" although the results are the same, the criteria corresponding to the aggregation key should be written in the WHERE clause, not in the HAVING clause.

--the "reason" ①where clause executes faster than having a having.

--② meaning: WHERE clause = the condition that corresponds to the specified row, having clause = the condition that corresponds to the specified group.

--------------------------------------

--Iv. sorting the results of the query

--1.order BY clause

--Syntax:
--select < column name 1>, < column name 2>,...
--from < table name >
--order by < sort datum column 1>, < sort datum column 2>

SELECT conbio_id, Conbio_price1
From Dbo.conbio
ORDER by Conbio_price1; --Ascending order

--sort key: The column name written in the ORDER BY clause.
--"Write order" select-------------

------------------

--2. Ascending (ASC) and Descending (DESC):

SELECT conbio_id, Conbio_price1
From Dbo.conbio
ORDER by Conbio_price1 DESC; --Descending order

--order by conbio_id ASC; --Descending order
--The order in the "notes" ORDER BY clause is arranged by default using ascending (ASC).

------------------

--3. Specifying multiple sort keys

SELECT conbio_id, Conbio_name, Conbio_price1, Conbio_price2
From Dbo.conbio
ORDER by Conbio_price1, Conbio_price2;

------------------

--4.null the order of values: When the sort key contains NULL, it is summarized at the beginning or the end.

------------------

--5. Using aliases in the SELECT clause in the sort key

SELECT conbio_id as ID, Conbio_name, Conbio_price1 as HT
From Dbo.conbio
ORDER by HT, ID;

--"Execution order" from-------------------

--The "memo" ORDER by clause can use the alias defined in the SELECT clause, and the GROUP by clause cannot use aliases.

------------------

Using aggregate functions in the--6.order by clause

SELECT conbio_varieties, COUNT (*)
From Dbo.conbio
GROUP by Conbio_varieties
ORDER by COUNT (*);

------------------

--7. Sorting is not recommended for column numbers, although it can be

SELECT conbio_id,
Conbio_name,
Conbio_varieties,
Conbio_price1,
Conbio_price2,
Conbio_date
From Dbo.conbio
ORDER by Conbio_price1 DESC, conbio_id;

------------------

SELECT conbio_id,
Conbio_name,
Conbio_varieties,
Conbio_price1,
Conbio_price2,
Conbio_date
From Dbo.conbio
ORDER by 4 DESC, 1; --Use the number of the column here, because of the inconvenience of reading, not recommended

--"Remarks" Do not use the column number in the ORDER by clause.

--------------------------------------

--Welcome to Personal public Number: Zkcops

--2018/04/16
　
By: Zkcops writing (hope to be helpful to you, reprint annotated source!) ）
--------------------------------------

"SQL. Basic Build-section III (3/4)"