Aggregates

The query builder also provides a variety of aggregate methods such as count, max, min, and sum. These methods take the headache out of setting up these common aggregate functions.

When executing any of the aggregate functions, any where restrictions on your query will still be applied.

Instead of returning a query, these methods return a simple value.

exists

Name	Type	Required	Default	Description
options	struct	false	{}	Any additional queryExecute options.

Returns true if the query returns any rows. Returns false otherwise.

QueryBuilder

query.from( "users" ).where( "username", "like", "jon%" ).exists();

SQL (MySQL)

SELECT COUNT(*) AS aggregate FROM `users` WHERE `username` LIKE 'jon%'

count

Name	Type	Required	Default	Description
column	string	false	"*"	The column on which to count records.
options	struct	false	{}	Any additional queryExecute options.

Returns an integer number of rows returned by the query.

QueryBuilder

query.from( "users" ).count();

SQL (MySQL)

SELECT COUNT(*) AS aggregate FROM `users`

SQL Server

SELECT COUNT(*) FROM [users]

max

Name	Type	Required	Default	Description
column	string	true		The column on which to find the max.
options	struct	false	{}	Any additional queryExecute options.

Returns the maximum value for the given column.

QueryBuilder

query.from( "users" ).max( "age" );

SQL (MySQL)

SELECT MAX(age) AS aggregate FROM `users`

min

Name	Type	Required	Default	Description
column	string	true		The column on which to find the min.
options	struct	false	{}	Any additional queryExecute options.

Returns the minimum value for the given column.

QueryBuilder

query.from( "users" ).min( "age" );

SQL (MySQL)

SELECT MIN(age) AS aggregate FROM `users`

sum

Name	Type	Required	Default	Description
column	string	true		The column to sum.
options	struct	false	{}	Any additional queryExecute options.

Returns the sum of all returned rows for the given column.

QueryBuilder

query.from( "employees" ).sum( "salary" );

SQL (MySQL)

SELECT SUM(salary) AS aggregate FROM `employees`

sumRaw

Name	Type	Required	Default	Description
column	string	true		The column to sum.
options	struct	false	{}	Any additional queryExecute options.

Returns the sum of all returned rows for the expression.

QueryBuilder

query.from( "accounts" ).sumRaw( "netAdditions + netTransfers" )

SQL (MySQL)

SELECT SUM(netAdditions + netTransfers) AS aggregate FROM `accounts`

columnList

Name	Type	Required	Default	Description
asQuery	boolean	false	false	Flag to retrieve the columnList as a query instead of an array.
datasource	string	false		Optional datasource to from which to retrieve the columnList.

Retrieves the columns for the configured table.

QueryBuilder

query.from( "users" ).columnList();

Result

[ "id", "firstName", "lastName", "username", "email", "password" ]