Skip to main content
Aggregate functions collapse multiple rows into a single summary value. They are always used together with GROUP BY, which groups rows sharing the same value in one or more fields before the aggregation is applied.

​
Aggregate vs. scalar functions

Scalar functionsAggregate functions
InputOne row at a timeAll rows in a group
OutputOne value per input rowOne value per group
Requires GROUP BYNoYes
ExamplesLOWER, ROUND, IFCOUNT, SUM, AVG

​
Reference

Fluent API methodFQL functionDescriptionDISTINCT support
count($field)COUNT(field)Count non-null values; count() with no argument counts all rows (COUNT(*))Yes (not with COUNT(*))
sum($field)SUM(field)Sum numeric valuesYes
avg($field)AVG(field)Arithmetic mean of numeric valuesNo
min($field)MIN(field)Minimum value in the groupYes
max($field)MAX(field)Maximum value in the groupYes
groupConcat($field, $sep)GROUP_CONCAT(field, sep)Concatenate values with a separator (default ,)Yes
collectObject($builder)COLLECT_OBJECT(expr [AS alias], … [ORDER BY …])Collect rows in each group into an array of structured objectsNo

​
Basic aggregation example

use FQL\Enum\Operator;

$query
    ->select('brand.code')->as('brand')
    ->count('id')->as('productCount')
    ->sum('price')->as('totalPrice')
    ->avg('price')->as('avgPrice')
    ->min('price')->as('minPrice')
    ->max('price')->as('maxPrice')
    ->groupConcat('name', '/')->as('products')
    ->groupBy('brand.code')
    ->having('productCount', Operator::GREATER_THAN, 5)
    ->orderBy('totalPrice', \FQL\Enum\Sort::DESC);

​
DISTINCT support

Passing true as the second argument (Fluent API) or prefixing the field with DISTINCT (FQL) causes the function to operate only on unique values, ignoring duplicates. 
$query
    ->count('category', true)->as('uniqueCategories')
    ->sum('price', true)->as('sumDistinct')
    ->min('price', true)->as('minDistinct')
    ->max('price', true)->as('maxDistinct')
    ->groupConcat('tag', ',', true)->as('uniqueTags')
    ->groupBy('brand.code');
DISTINCT is not supported with COUNT(*). Using it will throw an InvalidArgumentException.

​
HAVING clause

HAVING filters groups after aggregation, similar to how WHERE filters individual rows before aggregation. You can reference aggregated aliases in the HAVING condition. 
use FQL\Enum\Operator;

$query
    ->select('brand.code')->as('brand')
    ->count('id')->as('productCount')
    ->sum('price')->as('totalRevenue')
    ->max('price')->as('topPrice')
    ->groupBy('brand.code')
    ->having('productCount', Operator::GREATER_THAN, 10)
    ->or('totalRevenue', Operator::GREATER_THAN, 1000)
    ->or('topPrice', Operator::LESS_THAN, 500);
You can use HAVING without GROUP BY to filter the entire result set as a single group — useful for checking totals across all rows.

​
COUNT(*) vs COUNT(field)

ExpressionBehaviour
COUNT(*) / count()Counts every row in the group, including those with null values
COUNT(field) / count('field')Counts only rows where field is not null
COUNT(DISTINCT field)Counts distinct non-null values of field

​
COLLECT_OBJECT

Added in 3.1.0.
COLLECT_OBJECT collapses every row in a GROUP BY group into a single array of structured objects. Each row is projected through an inner mini-SELECT (with field aliases and arithmetic), and the resulting list can optionally be sorted with an inner ORDER BY.

​
Grammar

COLLECT_OBJECT(expr [AS alias], … [ORDER BY expr [ASC|DESC], …]) AS outer_alias
  • Inner items accept the full FQL expression syntax — column references, scalar functions (CONCAT, ROUND, IF, COALESCE, UPPER, LOWER, …), arithmetic (price * 1.21 AS price_with_vat), and aliases.
  • The optional inner ORDER BY accepts multiple sort keys with ASC / DESC and can reference both source columns and the aliases declared inside COLLECT_OBJECT(...).
  • The aggregate returns array<array<string, mixed>> per group.

​
FQL example

SELECT
    category.id AS category,
    COUNT(id) AS total,
    COLLECT_OBJECT(
        id,
        name,
        price * 1.21 AS price_with_vat
        ORDER BY price_with_vat DESC
    ) AS products
FROM json(./data/products.json).data.products
GROUP BY category.id

​
Fluent API

Build the inner projection with FQL\Query\Builder\CollectObject, then pass it to collectObject() and alias the result with as(): 
use FQL\Query\Builder\CollectObject;

$query
    ->select('category.id')->as('category')
    ->count('id')->as('total')
    ->collectObject(
        (new CollectObject())
            ->select('id', 'name', 'price * 1.21 AS price_with_vat')
            ->orderBy('price_with_vat')->desc()
    )->as('products')
    ->groupBy('category.id');
CollectObject::select() accepts the full FQL expression syntax — inline "expr AS alias" and comma-separated lists both work. orderBy(), asc(), and desc() chain just like on the main query.

​
Notes & limitations

  • Empty groups produce no output row, consistent with the other aggregates.
  • Null values propagate into the produced objects — unlike SUM / AVG, which skip them.
  • Stable sort preserves accumulation order on ties.
  • The inner ORDER BY sees both source columns and the aliases declared inside COLLECT_OBJECT(...).
  • DISTINCT, LIMIT, WHERE, and nested COLLECT_OBJECT are not supported and will throw a clear exception.
  • Aggregates inside COLLECT_OBJECT are accepted but rarely useful — they collapse the accumulated rows to a single output object, so COLLECT_OBJECT(SUM(x)) yields an array of length 1. Prefer scalar aggregates at the outer level alongside COLLECT_OBJECT for per-group summary numbers.