COUNT Function

Description

The COUNT function returns the number of of rows returned by a query. If an asterisk (*) is specified, the number of all rows satisfying the condition (including the rows with the NULL value) is returned. If the DISTINCT or UNIQUE keyword is specified in front of the expression, only the number of rows that have a unique value (excluding the rows with the NULL value) is returned after duplicates have been removed. Therefore, the value returned is always an integer and NULL is never returned.

A column that has collection type and object domain (user-defined class or multimedia class) can also be specified in the expression.

For analytic function examples, see the following analytic function examples. For more information, see Overview.

Syntax

COUNT ( * | [ { DISTINCT | DISTINCTROW } | UNIQUE | ALL ] expression )

• expression: Specifies an expression.
• ALL: Gets the number of rows given in the expression (default).
• DISTINCT or UNIQUE: Gets the number of rows without duplicates.

Example of Aggregate Function

The following example shows how to retrieve the number of Olympic Games that have a mascot in the demodb database.

SELECT COUNT(*)

FROM olympic

WHERE mascot IS NOT NULL;

     count(*)

=============

            9

Example of Analytic Function

The following example shows how to output the number of players whose nation_code is 'AUT' in demodb by accumulating the number of events when the event is changed. The last row shows the number of all players.

SELECT nation_code, event,name, COUNT(*) OVER (ORDER BY event) co

FROM athlete WHERE nation_code='AUT';

   nation_code           event                 name                           co

===============================================================================

  'AUT'                 'Athletics'           'Kiesl Theresia'                2

  'AUT'                 'Athletics'           'Graf Stephanie'                2

  'AUT'                 'Equestrian'          'Boor Boris'                    6

  'AUT'                 'Equestrian'          'Fruhmann Thomas'               6

  'AUT'                 'Equestrian'          'Munzner Joerg'                 6

  'AUT'                 'Equestrian'          'Simon Hugo'                    6

  'AUT'                 'Judo'                'Heill Claudia'                 9

  'AUT'                 'Judo'                'Seisenbacher Peter'            9

  'AUT'                 'Judo'                'Hartl Roswitha'                9

  'AUT'                 'Rowing'              'Jonke Arnold'                 11

  'AUT'                 'Rowing'              'Zerbst Christoph'             11

  'AUT'                 'Sailing'             'Hagara Roman'                 15

  'AUT'                 'Sailing'             'Steinacher Hans Peter'        15

  'AUT'                 'Sailing'             'Sieber Christoph'             15

  'AUT'                 'Sailing'             'Geritzer Andreas'             15

  'AUT'                 'Shooting'            'Waibel Wolfram Jr.'           17

  'AUT'                 'Shooting'            'Planer Christian'             17

  'AUT'                 'Swimming'            'Rogan Markus'                 18