The principle and function of MySQL aggregate function
Let me tell you a little bit about the principle and function of MySQL aggregate function. Have you known about similar topics before? If you are interested, let's take a look at this article. I believe it will be of some help to you after reading the principle and function of MySQL aggregation function.
Here are some aggregate functions that we often use. Please use them carefully and note the impact of sql_mode mode settings on query results. If sql_mode='', then:
The select create_time,test_name,max (moneys) from test_table group by test_name; query does not report an error, but it may be different from the expected result. The time does not match the maximum value. Some developers have written this before, and sql will report an error if it is above sql_mode='only_full_group_by',.
Avg () to find the average
Select test_name,avg (moneys) from test_table group by test_name; calculates the average amount by name
+-+ +
| | test_name | avg (moneys) |
+-+ +
| | Harold | 550000.175000 | |
| | Gwen | 170015.130000 | |
| | Benny | 915016.630000 | |
+-+ +
Max () to find the maximum
Select test_name,max (moneys) from test_table group by test_name; asks for the maximum amount in groups by person's name
+-+ +
| | test_name | max (moneys) |
+-+ +
| | Harold | 1000000.23 | |
| | Gwen | 170030.13 | |
| | Benny | 1660003.13 | |
+-+ +
Min () to find the minimum
Select test_name,min (moneys) from test_table group by test_name; groups by person's name to find the minimum amount
+-+ +
| | test_name | min (moneys) |
+-+ +
| | Harold | 100000.12 | |
| | Gwen | 170000.13 | |
| | Benny | 170030.13 | |
+-+ +
Sum () summation
Select test_name,sum (moneys) from test_table group by test_name; calculates the total amount by name
+-+ +
| | test_name | sum (moneys) |
+-+ +
| | Harold | 1100000.35 | |
| | Gwen | 340030.26 | |
| | Benny | 1830033.26 | |
+-+ +
Count () to find the number of rows
Select count () from test_table; directly calculates the total number of rows
+-+
| | count () |
+-+
| | 6 |
+-+
Select test_name,count () from test_table group by test_name; is grouped by person name to calculate the frequency.
+-+ +
| | test_name | count () |
+-+ +
| | Harold | 2 |
| | Gwen | 2 |
| | Benny | 2 |
+-+ +
Select test_name,count (distinct test_name) from test_table group by test_name; removes the weight first, and then calculates the frequency according to the name of the person.
+-- +
| | test_name | count (distinct test_name) |
+-- +
| | Harold | 1 | |
| | Gwen | 1 | |
| | Benny | 1 |
+-- +
6.group_concat () stitching data
Select test_name,group_concat (test_id), avg (moneys) from test_table group by test_name; grouped and spliced id by name
+-+
| | test_name | group_concat (test_id) | avg (moneys) | |
+-+
| | Harold | 1Pol 2 | 550000.175000 | |
| | Gwen | 3pr 5 | 170015.130000 | |
| | Benny | 4pr 6 | 915016.630000 | |
+-+
7. Calculate daily visits
Select * from T1
+-+
| | year | month | day | |
+-+
| | 2000 | 01 | 01 | |
| | 2000 | 01 | 20 | |
| | 2000 | 01 | 30 | |
| | 2000 | 02 | 02 | |
| | 2000 | 02 | 23 | |
| | 2000 | 02 | 23 | |
+-+
SELECT year,month,BIT_COUNT (BIT_OR (1
