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 neither ALL nor DISTINCT is specified.
  • A fast, but not exact alternative to COUNT(DISTINCT) is the function APPROXIMATE_COUNT_DISTINCT.

Examples

Aggregate Functions

SELECT 
    department, 
    COUNT(*) COUNT
FROM employee_table GROUP BY department;
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