Aggregate functions

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 functions	Aggregate functions
Input	One row at a time	All rows in a group
Output	One value per input row	One value per group
Requires `GROUP BY`	No	Yes
Examples	`LOWER`, `ROUND`, `IF`	`COUNT`, `SUM`, `AVG`

Reference

Fluent API method	FQL function	Description	DISTINCT 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 values	Yes
`avg($field)`	`AVG(field)`	Arithmetic mean of numeric values	No
`min($field)`	`MIN(field)`	Minimum value in the group	Yes
`max($field)`	`MAX(field)`	Maximum value in the group	Yes
`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 objects	No

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);

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,
    GROUP_CONCAT(name, '/') AS products
FROM json(./data/products.json).data.products
GROUP BY brand.code
HAVING productCount > 5
ORDER BY totalPrice 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');

SELECT
    COUNT(DISTINCT category) AS uniqueCategories,
    SUM(DISTINCT price) AS sumDistinct,
    MIN(DISTINCT price) AS minDistinct,
    MAX(DISTINCT price) AS maxDistinct,
    GROUP_CONCAT(DISTINCT tag, ',') AS uniqueTags
FROM json(./data/products.json).data.products
GROUP BY 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);

SELECT
    brand.code AS brand,
    COUNT(id) AS productCount,
    SUM(price) AS totalRevenue,
    MAX(price) AS topPrice
FROM json(./data/products.json).data.products
GROUP BY brand.code
HAVING
    productCount > 10
    OR totalRevenue > 1000
    OR topPrice < 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)

Expression	Behaviour
`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.

Get Started

Core Concepts

Querying

Functions Reference

Advanced

Aggregate functions

Aggregate vs. scalar functions

Reference

Basic aggregation example

DISTINCT support

HAVING clause

COUNT(*) vs COUNT(field)

COLLECT_OBJECT

Grammar

FQL example

Fluent API

Notes & limitations

​Aggregate vs. scalar functions

​Reference

​Basic aggregation example

​DISTINCT support

​HAVING clause

​COUNT(*) vs COUNT(field)

​COLLECT_OBJECT

​Grammar

​FQL example

​Fluent API

​Notes & limitations

Aggregate vs. scalar functions

Reference

Basic aggregation example

DISTINCT support

HAVING clause

COUNT(*) vs COUNT(field)

COLLECT_OBJECT

Grammar

FQL example

Fluent API

Notes & limitations