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.