Application of select query (III.)

Select subtotals
Another powerful feature of structured queries (SQL) is subtotals, which is the group clause; MySQL, of course, provides this functionality. I'm also going to say the use of the group clause in my database for the application of the Select query (ii) in the use of the join clause.

First, inquire about the number of transactions per customer. Count () is a function that is used with the group clause, and it acts as a count:

SELECT Customer,count (*) as Sale_count from sales GROUP by customer

The query returned may result in:

+----------+------------+
| Customer | Sale_count |
+----------+------------+
|     1 | 1 |
+----------+------------+
|     2 | 1 |
+----------+------------+

Represents a transaction for two customers with code 1 and 2 respectively.

Second, inquires the total amount of each customer's transaction. SUM () is a function that is used with the group clause, and it acts as a sum:

SELECT Customer,sum (Price*amount) as Total_price from sales GROUP by customer

The results of the query returned may be:

+----------+-------------+
| Customer | Total_price |
+----------+-------------+
|  1 | 12000.00 |
+----------+-------------+
|  2 | 12000.00 |
+----------+-------------+

Indicates that the two customers with code 1 and 2 each have a turnover of 12000 yuan. where sum (price*amount) indicates that price and amount are multiplied before the sum is added, i.e. the total price.

Check the average turnover per customer. AVG () is the average function:

SELECT Customer,avg (Price*amount) as Avg_price from sales GROUP by customer

The results of the query returned may be:

+----------+-----------+
| Customer | Avg_price |
+----------+-----------+
| 1 | 12000.00 |
+----------+-----------+
| 2 | 12000.00 |
+----------+-----------+

The average turnover of two customers with code 1 and 2 is 12000 yuan per transaction. Because I preset the amount of data is small, the results of the query is not very obvious, please understand the reader. You can add some data to the sales table at will, the larger the amount of data, the more obvious the result.

Inquire the largest and smallest turnover of each customer. The Max () and Min () functions are the functions that take the maximum and minimum values:

SELECT Customer,max (Price*amount) as Max_price,min (Price*amount) as Min_price from sales GROUP by customer

The results of the query returned may be:

+----------+-----------+-----------+
| Customer | Max_price | Min_price |
+----------+-----------+-----------+
| 1 | 12000.00 | 12000.00 |
+----------+-----------+-----------+
| 2 | 12000.00 | 12000.00 |
+----------+-----------+-----------+
　　
Check the average price of each type of goods sold.

SELECT Good_code,avg (Price) as Avg_price from sales GROUP by Good_code

Is that right? Wrong! This is to inquire about the average price of each item in the sales record of each type of goods, not the average price of all the goods actually sold; for us, what is useful is the weighted average price per sales quantity:

SELECT good_code,sum (price*amount)/sum (amount) as Avg_price from sales GROUP by Good_code

The results of the query returned may be:

+-----------+-----------+
| Good_code | Avg_price |
+-----------+-----------+
|  A0001 | 1200.00 |
+-----------+-----------+

The average price to be sold for each type of goods sold to different customers. Just add one more keyword to the group clause:

SELECT good_code,customer,sum (price*amount)/sum (amount) as Avg_price from sales GROUP by Good_code,customer

The results of the query returned may be:

+-----------+----------+-----------+
| Good_code | Customer | Avg_price |
+-----------+----------+-----------+
|    A0001 |  1 | 1200.00 |
+-----------+----------+-----------+
|    A0001 |  2 | 1200.00 |
+-----------+----------+-----------+

All customers and goods sold the same two records together to the average, resulting in the sale of different customers for each type of goods sold average price.