SQL--chater03 Aggregation and sorting

Data Description:

+-----------+------------+---------------+--------------+--------------+------------+|shohin_id|Shohin_mei|Shohin_bunrui|Hanbai_tanka|Shiire_tanka|Torokubi|+-----------+------------+---------------+--------------+--------------+------------+| 0001      |Shirt|Clothes|          + |           - |  the- the- - || 0002      |Hole Punching device|Office Supplies|           - |           the |  the- the- One || 0003      |Sports T-shirt|Clothes|         4000 |         2800 | NULL       || 0004      |Chopper|Kitchen utensils|          the |         2800 |  the- the- - || 0005      |Pressure cooker|Kitchen utensils|         6800 |          the |  the- on- the || 0006      |Fork|Kitchen utensils|           - |         NULL |  the- the- - || 0007      |Wiping the vegetable board|Kitchen utensils|          880 |          790 |  --Geneva- - || 0008      |Ball pen|Office Supplies|           - |         NULL |  the- One- One |+-----------+------------+---------------+--------------+--------------+------------+8Rowsinch Set(0.00Sec

aggregate queries on a table

Aggregation functions:

Count: Count the number of records in the table (number of rows).

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: Find the maximum value for the data in any column in the table.

Min: Find the minimum value of the data in any column in the table.

The results of the 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.

The aggregate function excludes null. However, the count (*) exception does not exclude null.

The Max/min function is suitable for almost all data type columns. The SUM/AVG function applies only to columns of numeric types.

You can use distinct in the parameters of the count function when you want to calculate the kind of merit.

You can delete duplicate data by using distinct in the parameters of the aggregate function.

to Group A table

SELECT Shohin_bunrui, COUNT (*) from  Shohin GROUP by shohin_bunrui;+---------------+----------+| Shohin_bunrui | COUNT (*) |+---------------+----------+| Office Supplies      |        2 | | Kitchen Utensils      |        4 | | Clothes          |        2 |+---------------+----------+

Gooup by is like a knife in a slit table.

The order in which clauses are written:

Selectàfromàwhereàgroup by

The order of the SQL clauses cannot be changed or replaced with each other.

SELECT Shiire_tanka, COUNT (*) from  Shohin GROUP by shiire_tanka;+--------------+----------+| Shiire_tanka | COUNT (*) |+--------------+----------+|         NULL |        2 | |          |        1 | |          |        1 | |          790 |        1 | |         2800 |        2 | |         |        1 |+--------------+----------+

　

When the aggregation key contains null, it appears in the result as an "indeterminate" row (a blank line).

When the GROUP by and where are used, the SELECT statement is executed in the following order:

From---WHERE---GROUP by---SELECT

When using an aggregate function, only the following three elements exist in the SELECT clause:

• Constant
• Aggregation functions
• specified in the GROUP by clause (i.e., the aggregation key)

The alias defined in the SELECT clause cannot be used in the GROUP by clause.

Because the SELECT clause is executed after the GROUP BY clause.

The display of the GROUP by clause result is unordered.

Aggregate functions are available only in the Select and HAVING clauses (and order by words).

specifying criteria for aggregate function results

HAVING clause:

SELECT < column name 1>, < column name 2>, < column name 3>,...

From < table name >

GROUP by < column name 1>, < column name 2>, < column name 3>,...

Having < grouping results corresponding conditions >

The order of the SELECT statement when using the HAVING clause:

SELECT---from---WHERE---GROUP by---have

The HAVING clause is written after the GROUP BY clause, and the order of execution within the DBMS is also after the GROUP BY clause.

SELECT Shohin_bunrui, COUNT (*) from  Shohin GROUP by shohin_bunruihaving COUNT (*) = 2;in_bunrui | COUNT (*) |+---------------+----------+| Office Supplies      |        2 | | Clothes          |        2 |+---------------+----------+select Shohin_bunrui, AVG (Hanbai_tanka) from  Shohin GROUP by shohin_bunruihaving AVG (Hanbai_tanka) >= 2500;ohin_bunrui | AVG (Hanbai_tanka) |+---------------+-------------------+| Kitchen Utensils      |         2795.0000 | | Clothes          |         2500.0000 |+---------------+-------------------+

　

The 3 elements that can be used in a HAVING clause are as follows:

• Constant
• Aggregation functions
• The column name (that is, the aggregation key) specified in the GROUP by clause

WHERE clause = the condition that corresponds to the specified row

HAVING clause = the condition that corresponds to the specified group

to sort the query structure

ORDER BY clause:

SELECT < column name 1>, < column name 2>, < column name 3>,...

From < table name >

Order BY < sort datum column 1>, < sort datum column 1>,...

In either case, the ORDER by clause needs to be written at the end of the SELECT statement. This is because the operation to sort the data must be performed when the result is about to be returned.

The order in which clauses are written:

SELECT---from---WHERE---GROUP by---have---ORDER by

Sorting in order BY is not specified by default in ascending order. ASC Ascending, desc descending.

When NULL is included in the sort key, it is summarized at the beginning or the end. (in MySQL, ascending is summarized at the beginning, and descending is summarized at the end)

The order in which the SELECT statement executes when using the HAVING clause:

From---WHERE---the GROUP by---have---SELECT---ORDER by

The order of the SELECT clause is executed after the GROUP BY clause, before the ORDER BY clause. Therefore, the alias defined in the SELECT statement cannot be recognized when the GROUP BY clause is executed. There is no such problem with executing the GROUP BY clause after the SELECT clause.

The columns and aggregate functions used in the SELECT clause can be used in the ORDER BY clause.

SELECT Shohin_mei, Hanbai_tanka, Shiire_tanka from  shohinorder by shohin_id;+------------+--------------+------- -------+| Shohin_mei | Hanbai_tanka | Shiire_tanka |+------------+--------------+--------------+| T-shirts        |         |          500 | | Hole Drilling Device     |          |          320 | | Sports T-shirt    |         4000 |         2800 | | Chopper       |         |         2800 | | Pressure Cooker     |         6800 |         5000 | | Fork       |          |         NULL | | Wiping the vegetable board     |          880 |          790 | | Ballpoint Pens     |          |         NULL |+------------+--------------+--------------+select Shohin_bunrui, COUNT (*) from  Shohin GROUP by Shohin_ Bunruiorder by COUNT (*), +---------------+----------+| Shohin_bunrui | COUNT (*) |+---------------+----------+| Office Supplies      |        2 | | Clothes          |        2 | | Kitchen Utensils      |        4 |+---------------+----------+

　　

　

　

SQL--chater03 Aggregation and sorting