Counts the number of rows in a group depending on the specified parameters.
COUNT_BIG( [ * | [ ALL | DISTINCT ] expression ] )
COUNT_BIG( [ * | [ ALL ] expression ]) OVER ( window-spec )
window-spec : see Syntax 2 instructions in the Remarks section below
* Return the number of rows in each group. COUNT_BIG(*) and COUNT_BIG() are semantically equivalent.
[ ALL ] expression Return the number of rows in each group where the value of expression is not null.
DISTINCT expression Return the number of distinct values of expression for all of the rows in each group where expression is not null.
COUNT_BIG returns a value of type BIGINT.
COUNT_BIG never returns the value NULL. If a group contains no rows, or if there are no non-null values of expression in a group, then COUNT_BIG returns 0.
It is recommended that you use the COUNT_BIG function when counting large result sets, the result might have more rows, or there is a possibility of overflow. Otherwise, use the COUNT function, which has a maximum value of 2147483647.
Syntax 2 represents usage as a window function in a SELECT statement. As such, elements of window-spec can be specified either in the function syntax (inline), or in conjunction with a WINDOW clause in the SELECT statement. See the window-spec definition in WINDOW clause.
For more information about using window functions in SELECT statements, including working examples, see Window functions.
For more information about specifying a window specification in an OVER clause, see Window definition: inlining using the OVER clause and WINDOW clause.
SQL/2008 Vendor extension.
SQL Anywhere does not support optional SQL/2008 feature F442, "Mixed column references in set functions". SQL Anywhere does not permit the arguments of an aggregate function to include both a column reference from the query block containing the COUNT_BIG function, combined with an outer reference. See Aggregate functions and outer references. For an example, see the AVG function [Aggregate]
The following statement returns each unique city, and the number of employees working in that city.
SELECT City, COUNT_BIG( * ) FROM Employees GROUP BY City; |
![]() |
Discuss this page in DocCommentXchange.
|
Copyright © 2010, iAnywhere Solutions, Inc. - SQL Anywhere 12.0.0 |