GroupBy()
To group by a given field, use the groupBy() method.
$query->groupBy('uid');
The code above instructs the query to group by the uid field. Note that the field name here must be the alias created by the addField() or addExpression() methods, so in most cases you will want to use the return value from those methods to ensure the correct alias is used.
To get the count of rows grouped by a field, such as uid, you can do the following:
$query->addExpression('count(uid)', 'uid_node_count');
To group by multiple fields, simply call groupBy() several times in the desired order.
Having
You can add a condition on aggregated values.
$query->having('COUNT(uid) >= :matches', [':matches' => $limit]);
In this example, it will find cases where the number of uid identifiers is greater than or equal to $limit. Note that the first parameter of having is not filtered before being sent to the database, so user values must be passed through the second parameter.
Examples of groupBy and having
The following code counts the number of nodes per UID:
$query = $connection->select('node', 'n')
->fields('n', ['uid']);
$query->addExpression('count(uid)', 'uid_node_count');
$query->groupBy("n.uid");
$query->execute();
The following block of code takes the previous example of counting nodes per uid and restricts the results to uids that have at least 2 records.
$query = $connection->select('node', 'n')
->fields('n',['uid']);
$query->addExpression('count(uid)', 'uid_node_count');
$query->groupBy("n.uid");
$query->having('COUNT(uid) >= :matches', [':matches' => 2]);
$results = $query->execute();