Arguments to the COUNT Functions

The COUNT function accepts as its argument the same expressions that are allowed in the argument list of other built-in aggregate functions, as well as the asterisk (*) notation that only COUNT supports. The following categories of built-in expressions are supported as the argument to COUNT, as illustrated in the following examples:

  • Arithmetic Expressions
    COUNT(times(informix.sysfragments.evalpos,2))
    
    SELECT COUNT(a+1), COUNT(2*a), COUNT(5/a), COUNT(times(a, 2)) FROM myTable;
  • Bitwise Logical Functions
    COUNT(BITAND(informix.systables.flags,1)) 
    
    SELECT COUNT(BITAND(a,1)), COUNT(BITOR(8, 20)), COUNT(BITXOR(41, 33)),
           COUNT(BITANDNOT(20,-20)), COUNT(BITNOT(8)) FROM myTable;
  • Cast Expressions
    COUNT(NULL::int) 
  • Conditional Expressions
    COUNT(CASE WHEN stock.description = "baseball gloves" THEN 1 ELSE NULL END)
    
    SELECT COUNT(CASE WHEN s=14 THEN 1 ELSE NULL END) AS cnt14 FROM all_types;
    SELECT COUNT(NVL (ch, 'Addr unk')) FROM all_types;
    SELECT COUNT(NULLIF(ch, NULL)) FROM all_types;
  • Constant Expressions
    COUNT(CURRENT_ROLE) 
    COUNT(DATETIME (2007-12-6) YEAR TO DAY)
    
    SELECT COUNT("XX"), COUNT(99),COUNT("t") FROM sysmaster:sysdual;
    SELECT COUNT(SET{6, 9, 9, 4}) FROM sysmaster:sysdual;
    SELECT COUNT("ROW(7, 3, 6.0, 2.0)") FROM sysmaster:sysdual;
    SELECT COUNT(USER), COUNT(CURRENT), COUNT(SYSDATE) from sysmaster:sysdual;
    SELECT COUNT(CURRENT_ROLE), COUNT(DEFAULT_ROLE) from sysmaster:sysdual;
    SELECT COUNT(DBSERVERNAME), COUNT(TODAY), COUNT(CURRENT) from sysmaster:sysdual;
    SELECT COUNT(DATETIME (2007-12-6) YEAR TO DAY) from sysmaster:sysdual;
    SELECT COUNT(INTERVAL (16) DAY TO DAY) FROM sysmaster:sysdual;
    SELECT COUNT(5 UNITS DAY) FROM sysmaster:sysdual;
  • Function Expressions
    COUNT(LENGTH ('abc') + LENGTH (stock.description}
    COUNT(DBINFO('sessionid'))
    COUNT(user_proc())  --> Here proc() is a user-defined routine.
  • Column Expressions
    COUNT(informix.sysfragauth.fragment)
You can also use the asterisk (*) character, or a column name, or a column name with the ALL, DISTINCT, or UNIQUE aggregate scope qualifiers as the argument to the COUNT function to retrieve different types of information about a table. The table below summarizes the meaning of each of the following forms of the COUNT function with an asterisk or column name argument.
COUNT Function Description
COUNT (*) Returns the number of rows that satisfy the query. If you do not specify a WHERE clause, this function returns the total number of rows in the table.
COUNT (DISTINCT) or COUNT (UNIQUE) Returns the number of unique non-NULL values in the specified column
COUNT (column) or COUNT (ALL column) Returns the total number of non-NULL values in the specified column

Some examples can help to show the differences among the various forms of the COUNT function that reference a column. Most of the following examples query against the ship_instruct column of the orders table in the stores_demo demonstration database. For information on the schema of the orders table and the data values in the ship_instruct column, see the description of the demonstration database in the HCL OneDB™ Guide to SQL: Reference.