Grouping and aggregations

FiQueLa supports SQL-style grouping and aggregation. Use groupBy() to collapse rows into groups, aggregate functions to compute group-level summaries, and having() to filter the aggregated results.

WHERE vs HAVING

The key distinction:

WHERE — filters rows before grouping. Only rows that pass the filter are included in the groups.
HAVING — filters groups after aggregation. Conditions can reference aggregate aliases.

use FQL\Enum\Operator;

$query->count('id')->as('total')
    ->sum('price')->as('revenue')
    ->where('active', Operator::EQUAL, true)     // pre-aggregation filter
    ->groupBy('category')
    ->having('total', Operator::GREATER_THAN, 5); // post-aggregation filter

groupBy()

Group rows by one or more fields. Dot notation is supported for nested fields:

$query->groupBy('category.id');
$query->groupBy('brand.code', 'category.id');

FiQueLa 3.0 — groupBy() accepts any SQL expression, including function calls and infix arithmetic. The expression is evaluated for each row and the result is used as the grouping key.

// Group by computed year
$query->groupBy('YEAR(date)');

// Group by lowercased value
$query->groupBy('LOWER(category)');

// Group by an arithmetic bucket
$query->groupBy('FLOOR(price / 100)');

The same applies to aggregate helpers — sum(), avg(), min(), max(), and groupConcat() accept full expressions:

$query->sum('price * qty')->as('revenue')
    ->avg('price + vat')->as('avg_gross')
    ->groupBy('YEAR(date)');

DISTINCT and GROUP BY cannot be used together. Attempting to combine them throws a QueryLogicException.

Aggregate functions

Fluent method	FQL function	Description
`count($field, $distinct)`	`COUNT(field)` / `COUNT(DISTINCT field)`	Count rows
`sum($field, $distinct)`	`SUM(field)` / `SUM(DISTINCT field)`	Sum of values
`avg($field)`	`AVG(field)`	Average value
`min($field, $distinct)`	`MIN(field)` / `MIN(DISTINCT field)`	Minimum value
`max($field, $distinct)`	`MAX(field)` / `MAX(DISTINCT field)`	Maximum value
`groupConcat($field, $sep, $distinct)`	`GROUP_CONCAT(field, sep)` / `GROUP_CONCAT(DISTINCT field, sep)`	Concatenate values
`collectObject($builder)`	`COLLECT_OBJECT(expr [AS alias], … [ORDER BY …])`	Collect each group into an array of structured objects (see Aggregate functions)

All aggregate methods accept an optional bool $distinct parameter (except avg()). groupConcat() also accepts a separator string.

Examples

use FQL\Enum\Operator;

$query->select('category.id')->as('category')
    ->count('id')->as('total')
    ->sum('price')->as('revenue')
    ->avg('price')->as('avgPrice')
    ->min('price')->as('minPrice')
    ->max('price')->as('maxPrice')
    ->groupConcat('name')->as('names')
    ->groupBy('category.id')
    ->having('total', Operator::GREATER_THAN, 10)
    ->or('revenue', Operator::GREATER_THAN, 1000);

SELECT
    category.id AS category,
    COUNT(id) AS total,
    SUM(price) AS revenue,
    AVG(price) AS avgPrice,
    MIN(price) AS minPrice,
    MAX(price) AS maxPrice,
    GROUP_CONCAT(name) AS names
FROM json(products.json).data.products
GROUP BY category.id
HAVING total > 10
    OR revenue > 1000

DISTINCT in aggregates

Pass true as the last argument to count, sum, min, max, or groupConcat only unique values:

$query->count('id', true)->as('uniqueCount')
    ->sum('price', true)->as('uniqueRevenue')
    ->min('price', true)->as('uniqueMin')
    ->max('price', true)->as('uniqueMax')
    ->groupConcat('name', ',', true)->as('uniqueNames');

SELECT
    COUNT(DISTINCT id) AS uniqueCount,
    SUM(DISTINCT price) AS uniqueRevenue,
    MIN(DISTINCT price) AS uniqueMin,
    MAX(DISTINCT price) AS uniqueMax,
    GROUP_CONCAT(DISTINCT name, ",") AS uniqueNames
FROM json(products.json).data.products

Real-world example

This example comes from the XML example — grouping customers by age and computing average spending:

use FQL\Stream;
use FQL\Query;

$xml = Stream\Provider::fromFile('./examples/data/customers.xml');

$ageVsSpent = $xml->query()
    ->select('age.value')->as('customerAge')
    ->avg('spent.value')->as('avgSpent')
    ->round('avgSpent', 2)->as('avgSpentRounded')
    ->from('customers.customer')
    ->groupBy('age.value')
    ->orderBy('avgSpent')->desc()
    ->limit(10);

Equivalent FQL:

SELECT
    age.value AS customerAge,
    AVG(spent.value) AS avgSpent,
    ROUND(avgSpent, 2) AS avgSpentRounded
FROM xml(./examples/data/customers.xml).customers.customer
GROUP BY age.value
ORDER BY avgSpent DESC
LIMIT 10

Get Started

Core Concepts

Querying

Functions Reference

Advanced

Grouping and aggregations

WHERE vs HAVING

groupBy()

Aggregate functions

Examples

DISTINCT in aggregates

Real-world example

​WHERE vs HAVING

​groupBy()

​Aggregate functions

​Examples

​DISTINCT in aggregates

​Real-world example

WHERE vs HAVING

groupBy()

Aggregate functions

Examples

DISTINCT in aggregates

Real-world example