Shulou(Shulou.com)06/01 Report--

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

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.