COUNT

The COUNT function computes the number of times an item (value) appears in a group of values. For the examples that follow, please reference the orders table within the order entry (oe) schema at livesql.oracle.com. The contents of this table can also be found below. This table contains one row for each order placed by a customer.

COMPUTE THE TOTAL NUMBER OF ORDERS

SELECT
    COUNT(*) AS order_count
FROM
    oe.orders;

In the example code above, note that a GROUP BY clause is not present because the code returns a single row representing the total number of orders across the entire orders table (105). Here, we’ve taken advantage of knowing each row within the orders table represents a single order. Therefore, to compute the total number of orders within the orders table, simply the number of rows needs to be computed. Keep this in mind for the examples that follow.

COMPUTE THE TOTAL NUMBER OF ORDER FOR EACH CUSTOMER

SELECT
    oe.orders.customer_id,
    COUNT(*) AS order_count
FROM
    oe.orders
GROUP BY
    oe.orders.customer_id;

Computing the total number of orders for each customer requires that we simply build onto our previous solution. A GROUP BY clause is necessary to retrieve the number of orders for each customer. The result set will contain one row of data per customer. Within each row will be the customer ID and the corresponding order count value.

RETRIEVE ONLY THOSE CUSTOMERS WITH FIVE OR MORE ORDERS

SELECT
    oe.orders.customer_id,
    COUNT(*) AS order_count
FROM
    oe.orders
GROUP BY
    oe.orders.customer_id
HAVING
    COUNT(*) >= 5;

Like the approach taken in the previous example, simply build onto the previous solution. A HAVING clause is necessary to filter the grouped rows (i.e., the individual rows containing each customer’s order count). Filter by the number of rows corresponding to each customer (i.e., COUNT(*)).

RETRIEVE THE TOP CUSTOMER(S) IN TERMS OF ORDER COUNT

-- Using a subquery within a subquery.
SELECT
    oe.customers.customer_id,
    COUNT(oe.orders.order_id) AS order_count
FROM
    oe.customers
    INNER JOIN
    oe.orders
        ON oe.customers.customer_id = oe.orders.customer_id
GROUP BY
    oe.customers.customer_id
HAVING
    COUNT(oe.orders.order_id) = (
		SELECT
			MAX(order_counts.order_count)
		FROM (
			SELECT
				COUNT(oe.orders.order_id) AS order_count
			FROM
				oe.orders
			GROUP BY
				oe.orders.customer_id
		) order_counts
	);

-- Using a subquery in the HAVING clause to solve (nested-aggregate).
SELECT
    oe.customers.customer_id,
    COUNT(oe.orders.order_id) AS order_count
FROM
    oe.customers
    INNER JOIN
    oe.orders
    	ON oe.customers.customer_id = oe.orders.customer_id
GROUP BY
    oe.customers.customer_id
HAVING
    COUNT(oe.orders.order_id) = (
		SELECT
			MAX(COUNT(oe.orders.order_id))
		FROM
			oe.orders
		GROUP BY
			oe.orders.customer_id
	);

-- Using subquery in the HAVING clause with the modified comparison operator, ALL.
SELECT
    oe.orders.customer_id,
    COUNT(*) AS order_count
FROM
    oe.orders
GROUP BY
    oe.orders.customer_id
HAVING
    COUNT(*) >= ALL (
		SELECT
			COUNT(*)
		FROM
			oe.orders
		GROUP BY
			oe.orders.customer_id
	);

The first and second solutions rely on a subquery in the HAVING clause. The subquery returns a single value representing the largest number of orders placed by any given customer. The number of orders placed by each customer is subsequently compared to this value with rows in the outer query containing the same value being kept in the result-set. Take caution in implementing the second solution as it contains a nested aggregate. Many RDBMS do not permit the direct nesting of aggregates and require the additional step seen in the first solution where the number of orders placed for each customer is computed, treated as a derived table or temporary result-set, and finally the maximum of those order counts is computed. The third solution above utilizes a subquery in the HAVING clause in conjunction with the modified comparison operator, ALL. The subquery returns a list of values with each value representing an individual customer’s order count. Each customer’s order count computed in the outer query is compared to the list generated by the subquery. If the customer’s order count value is greater than or equal the all the values in the list, then the row for that customer is included in the result set. It can be inferred that if the customer’s order count value, when compared to each individual value generated by the subquery, is greater than or equal all of the values, then it must be the largest value.

COUNT(*) vs. COUNT(attribute)
When using the COUNT function, it is important to understand the difference in the function’s behavior when the argument passed to the function is an attribute instead of the “*” character. When an attribute is passed to the function, the count returned represents the number of rows with non-null values for the specified attribute while COUNT(*) simply represents the number of rows returned. For clarity, see the example code and corresponding output below.

SELECT
	COUNT(*) AS order_count,
	COUNT(oe.orders.sales_rep_id) AS assisted_order_count
FROM
	oe.orders;

In the output above, COUNT(*) returns the number of rows (i.e., orders) within the orders table. When the sales representative ID attribute is passed into the COUNT function, the number of rows with a sales representative ID value populated is returned (70). This value tells us seventy orders were placed with the assistance of a sales representative.

Knowledge Check
Need more practice or simply wish to test your understanding? Give a few of the practice problems I’ve provided in the SQL Practice Problems section a try.