Aggregation query (query builder 7)

In applications, we often use some statistical data, such as the number of current users (or those who meet certain conditions), the maximum points of all users, the average score of users, etc. ThinkPHP is These statistical operations provide a series of built-in methods, including:

Method	Description
count	Count the number, the parameter is the field name to be counted (optional)
max	Get the maximum value , the parameter is the field name to be counted (required)
min	Get the minimum value, the parameter is the field name to be counted (required)
avg	Get the average value, the parameter is the field name to be counted (required)
sum	Get the total score, The parameter is the name of the field to be counted (required)

If there is no data in the aggregation method, the default is 0, and the aggregation query can be combined with other query conditions

Usage example

Get the number of users:

Db::table('think_user')->count();

The actual generated SQL statement is:

SELECT COUNT(*) AS tp_count FROM `think_user` LIMIT 1

or generated based on field statistics:

Db::table('think_user')->count('id');

The SQL statement is:

SELECT COUNT(id) AS tp_count FROM `think_user` LIMIT 1

Get the user’s maximum points:

Db::table('think_user')->max('score');

The generated SQL statement is:

SELECT MAX(score) AS tp_max FROM `think_user` LIMIT 1

If the maximum value you want to get is not a numerical value, you can use The second parameter turns off forced conversion

Db::table('think_user')->max('name',false);

Get the minimum points of users whose points are greater than 0:

Db::table('think_user')->where('score', '>', 0)->min('score');

Like the max method, min also supports the second parameter usage

Db::table('think_user')->where('score', '>', 0)->min('name',false);

Get the user’s average score:

Db::table('think_user')->avg('score');

The generated SQL statement is:

SELECT AVG(score) AS tp_avg FROM `think_user` LIMIT 1

Statistics of the user’s total score:

Db::table('think_user')->where('id',10)->sum('score');

The generated SQL statement is:

SELECT SUM(score) AS tp_sum FROM `think_user` LIMIT 1

If you want to use group for aggregation query, you need to implement the query yourself, for example:

Db::table('score')->field('user_id,SUM(score) AS sum_score')->group('user_id')->select();