Aggregate functions

Aggregate functions are used to perform calculations on a set of values and return a single value. The QueryBuilder library provides support for most common and widely used aggregate functions.

TIP

You can use aggregate functions in a chain with other methods of the Select class.

Count

The count() method in the Select class statement is used to add a COUNT aggregate function to your SQL query.

The method takes two parameters:

$column: The column you want to count (default is '*' for all columns)
$alias: An optional alias for the result column (default is null)

php

$query = new QueryBuilder();

$select = $query->select()->from( 'posts' )->count( 'ID', 'total_posts' );

$result = $select->fetch_column();

Sum

The sum() method in the Select class statement is used to add a SUM aggregate function to your SQL query.

The method takes two parameters:

$column: The column you want to sum (required)
$alias: An optional alias for the result column (default is null)

php

$query = new QueryBuilder();

$select = $query
	->select()
	->from( 'orders' )
	->sum( 'total_amount', 'total_sales' );

$result = $select->fetch_column();

Average

The avg() method in the Select class statement is used to add an AVG aggregate function to your SQL query.

The method takes two parameters:

$column: The column you want to average (required)
$alias: An optional alias for the result column (default is null)

php

$query = new QueryBuilder();

$select = $query
	->select()
	->from( 'products' )
	->avg( 'price', 'average_price' );

$result = $select->fetch_column();

Minimum

The min() method in the Select class statement is used to add a MIN aggregate function to your SQL query.

The method takes two parameters:

$column: The column you want to find the minimum value of (required)
$alias: An optional alias for the result column (default is null)

php

$query = new QueryBuilder();

$select = $query
	->select()
	->from( 'products' )
	->min( 'price', 'lowest_price' );

$result = $select->fetch_column();

Maximum

The max() method in the Select class statement is used to add a MAX aggregate function to your SQL query.

The method takes two parameters:

$column: The column you want to find the maximum value of (required)
$alias: An optional alias for the result column (default is null)

php

$query = new QueryBuilder();

$select = $query
	->select()
	->from( 'employees' )
	->max( 'salary', 'highest_salary' );

$result = $select->fetch_column();