COUNT() - Count elements

Function group: aggregate function

COUNT() counts the elements in a set of values. NULL values are not included in the count.

---

COUNT ([ALL | DISTINCT ] expression )

---

ALL

All values are taken into account, including duplicate value.

DISTINCT

Only unique values are taken into account. Duplicate values are ignored.

expression

Numeric expression, alphanumeric expression, national expression or time value expression (see section "Aggregate functions" for information on restrictions).

Result

Without GROUP BY clause:

Number of values in the set returned by expression (see "Calculating aggregate functions").

With GROUP BY clause:

Returns the number of values in each group.

Data type: DECIMAL (31, 0)

Examples

SELECT without GROUP BY:

Determine the number of different service descriptions in the SERVICE table (result: 7):

SELECT COUNT(DISTINCT service_text) FROM service

SELECT with GROUP BY:

Count the number of different services for each order number:

SELECT order_num, COUNT(DISTINCT service_text) FROM service 
   GROUP BY order_num
   order_num
   200                 2
   211                 4
   260                 2