The SQL Count Function Explained With 7 Examples

One of the most useful aggregate functions in SQL is the COUNT() function. If you are new to SQL and want to learn about the various ways to use the COUNT() function with some practical examples, this article is for you.

The COUNT() function is one of the most useful aggregate functions in SQL. Counting the total number of orders by a customer in the last few days, the number of unique visitors who bought a museum ticket, or the number of employees in a department, can all be done using the COUNT() function.

But, before we jump into some real-world SQL COUNT() examples, take a look at the sample syntax for using the COUNT() function. The basic syntax is:

SELECT COUNT([DISTINCT] <column_name>)
FROM <table_name>
WHERE <conditions>;

The COUNT() function appears in the SELECT part of the query and can accept a column name as an argument. Optionally, you can specify the DISTINCT keyword to count only the unique occurrences of the values. Don’t worry if you don’t understand at this stage; I will explain the use of DISTINCT with COUNT() later in this article with examples.

Let’s dive in to understand the SQL COUNT() function and the various ways to use it with some real-world use cases. For demonstration, say you work in a company that sells cars and motorcycles. You have the following dataset at your disposal.

The products table:

The table has 5 columns with the following details:

1. product_code: A unique code that identifies each product.
2. product_name: The name of the car or the motorcycle.
3. product_line: A classification that identifies whether the product is a car or a motorcycle.
4. product_vendor: A code to identify the corresponding vendor for the product.
5. buy_price: The price the vendor charges for the product.

Now, let’s say you want to find out the number of products in the table. This is where the COUNT() function can help you. The query looks something like this:

SELECT COUNT(product_code)
FROM products;

The output:

In this query, SQL counts all the values for product_code in the table products and returns the total number. This is because we passed the product_code column as an argument to our COUNT() function. Alternatively, if you just want to calculate the number of rows in the products table, you can do something like this:

SELECT COUNT(*)
FROM products;

The output:

Here, we used “*” as the argument to the function, which simply tells SQL to count all the rows in the table.

Now, say you want to count all the product lines in the table. Based on what you learned in the previous example, you’d probably write something like this.

SELECT COUNT(product_line)
FROM products;

The output:

The query returned 5 as the output because SQL counted one value of product_line for each row of the table. It is important to note that the COUNT() function does not account for duplicates by default. For instance, the products table has only two unique values for the product_line column – “Motorcycles” and “Classic Cars” – but our query still returned 5 as the value. So, if you want to count only the unique values in the table, you have to use the DISTINCT keyword with your COUNT() function.

The query:

SELECT COUNT(DISTINCT product_line)
FROM products;

The output:

In this query, since we explicitly specified the DISTINCT keyword in the argument to the COUNT() function, SQL counts only the unique values of product_line.

You can also use conditions in the WHERE clause to count only specific rows. For example, say you want to find out the number of products that belong to the product line “Motorcycles”. To do this, you can use the following query:

SELECT COUNT(product_code)
FROM products
WHERE product_line = ‘Motorcycles’;

The output:

Here, SQL first filters all the rows where the value of product_line is “Motorcycles”. It then counts the number of product codes.

One important thing to note is that the SQL COUNT() function does not consider NULL while counting. In the products table, the product_vendor value for the product_code “S10_4757” is NULL. So, the following query returns the value 4 and not 5.

SELECT COUNT(product_vendor)
FROM products;

The output:

While the values “HLD”, “CMC”, “HMD”, and “RSD” are included in the count, NULL is excluded.

The SQL COUNT() With a GROUP BY Clause

If you work (or aspire to work) as an analyst and use SQL to do data analysis, then in most cases you use the COUNT() function with a GROUP BY clause.

The GROUP BY clause in SQL is used to aggregate (group) data together in groups to help calculate aggregated metrics. Aggregate functions available in SQL are COUNT(), SUM(), AVG(), MIN(), and MAX(). These functions return a single value for multiple rows. Aggregation is important because it may not always be feasible for you to go through each row to generate insights.

Grouping customers based on their specific needs and behaviors (segmentation), grouping products into different types, or grouping various people based on their income, are all examples of aggregation.

Let’s take an example using the COUNT() function to understand this better. Say you want to calculate the number of products in each product line.

The query:

SELECT product_line,
	 COUNT(product_code)
FROM products
GROUP BY product_line;

The output:

In this query, SQL first gets all the unique values for product_line as the first column, then counts the number of products (product_code) belonging to each of them. In short, SQL aggregates all rows by product line and then uses the aggregate function (COUNT() in this case) to calculate the number of products for each product line.

It is important to specify all the columns (except the aggregate function part) you use in the SELECT part of the query in the GROUP BY clause. Otherwise, SQL throws an error. You can read through this article to understand common errors with GROUP BY clauses.

So far, the examples I have shown in this article have the COUNT() function in the SELECT part of the query. However, COUNT() is a versatile function; you can use it in various ways and in different parts of the query. Let’s take a look.

The SQL COUNT() With Condition in a HAVING Clause

The HAVING clause is used to filter groups based on conditions for the aggregate function. For instance, say you want to return only those aggregated groups with a count above a certain number.

With our dataset, imagine you want to only look at those product lines with more than 2 products.

The query:

SELECT product_line,
	   COUNT(product_code)
FROM products
GROUP BY product_line
HAVING COUNT(product_code)>2;

The output:

The query works similarly to the previous query, in which SQL groups rows based on unique values of product_line and then returns the number of products belonging to each product line using the COUNT() function. However, since we have chosen to keep only the product lines with more than two products, we do not have the “Classic Cars” product line (which has only 2 products) in the results.

The SQL COUNT() in an ORDER BY Clause

You can also have a case where you want to view all the product lines, but you want to arrange them by the number of products they have. Here, we need to use the COUNT() function in the ORDER BY clause.

The query:

SELECT   product_line,
	   COUNT(product_code)
FROM     products
ORDER BY COUNT(product_line);

The output:

In this query, SQL returns unique product lines as groups and the number of products in each product line as the count. However, because of the ORDER BY clause, the results are arranged in ascending order by the COUNT() function.

The SQL COUNT() With Condition in a WHERE Clause

The COUNT() function cannot be used directly in a WHERE clause. So, for example, if you want to retrieve all the details of products that belong to a product line with at least 3 products, you cannot use this query.

SELECT *
FROM products
WHERE COUNT(product_line)>=3;

The output:

ERROR 1111 (HY000): Invalid use of group function

You cannot accomplish this with just GROUP BY and HAVING clauses directly, either. This is because that just gives you grouped rows but not all the details about the products as required here.

Instead, you have to use something known as a subquery (a query inside the main query). This is what your query looks like.

SELECT *
FROM products
WHERE product_line in (SELECT product_line
    			   FROM products
			   GROUP BY product_line
   HAVING COUNT(product_code)>=3);

The output:

Ready to Use the COUNT() Function?

Now that you have read through this article, you should have a decent understanding of how to use the COUNT() function. Once you start using the function in your queries, your understanding improves further.

If you are new to SQL and want to create a strong foundation before you jump into practical jobs, I recommend this track from LearnSQL.com. I like this one particularly because it offers comprehensive coverage of basic concepts and gives you a base for growing your career.

Practice plays a huge role in your learning when it comes to SQL. So, keep practicing different kinds of queries, expand your boundaries, and keep growing.

Of course, the first step is the hardest. The good thing is that there is no shortage of great learning material on LearnSQL.com to help you in your journey. So, start today!