COUNT
Purpose
This function returns the number of rows in the result set of a SQL query.
Syntax
count ::=
Usage Notes
- If * is specified, Exasol counts all the rows.
- If COUNT contains one or more expressions expr, Exasol does not count the NULL values. If you use tuples of several expression, Exasol does not count tuples that consist only of NULL values.
- Exasol counts duplicate values of these expr or tuple of expressions only once with DISTINCT. It counts all occurrences with ALL. The default behavior is ALL if the functions contains neither ALL nor DISTINCT.
- The default value is ALL if neither ALL nor DISTINCT is specified.
- A fast, but not exact alternative to COUNT(DISTINCT) is the function APPROXIMATE_COUNT_DISTINCT.
Examples
Aggregate Functions
Result
DEPARTMENT | COUNT |
ACCOUNTS | 5 |
HR | 4 |
Analytic Function
SELECT
id, department,
COUNT(*) OVER (PARTITION BY department order by age) COUNT
FROM employee_table ORDER BY department, age;
Result
ID | DEPARTMENT | COUNT |
2003 | ACCOUNTS | 1 |
2001 | ACCOUNTS | 2 |
2002 | ACCOUNTS | 3 |
2004 | ACCOUNTS | 5 |
2005 | ACCOUNTS | 5 |
1001 | HR | 2 |
1004 | HR | 2 |
1002 | HR | 3 |
1003 | HR | 4 |