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.
toSQL	boolean	false	false	Returns the query as SQL, if true, instead of executing it.

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%'

existsOrFail

Name	Type	Required	Default	Description
options	struct	false	{}	Any additional queryExecute options.
errorMessage	string	false		An optional string error message.

Returns true if the query returns any rows. Throws a RecordNotFound exception otherwise.

QueryBuilder

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

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.
defaultValue	any	false	0	The default value for the COUNT query, if no records are returned.
options	struct	false	{}	Any additional queryExecute options.
toSQL	boolean	false	false	Returns the query as SQL, if true, instead of executing it.

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.
defaultValue	any	false		The default value for the MAX query, if no records are returned.
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.
defaultValue	any	false		The default value for the MIN query, if no records are returned.
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.
defaultValue	any	false	0	The default value for the SUM query, if no records are returned.
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" ]