21st Oct 2021 8 minutes read The SQL Count Function Explained With 7 Examples Himanshu Kathuria sql learn sql aggregate functions Table of Contents The SQL COUNT() With a GROUP BY Clause The SQL COUNT() With Condition in a HAVING Clause The SQL COUNT() in an ORDER BY Clause The SQL COUNT() With Condition in a WHERE Clause Ready to Use the COUNT() Function? 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: product_codeproduct_nameproduct_lineproduct_vendorbuy_price S10_1678Harley Davidson ChopperMotorcyclesMLD4881 S10_1949Alpine Renault 1300Classic CarsCMC9858 S10_2016Moto Guzzi 1100iMotorcyclesHMC6899 S10_4698Harley Davidson Eagle DragMotorcyclesRSD9102 S10_4757Alfa Romeo GTAClassic Carsnull8568 The table has 5 columns with the following details: product_code: A unique code that identifies each product. product_name: The name of the car or the motorcycle. product_line: A classification that identifies whether the product is a car or a motorcycle. product_vendor: A code to identify the corresponding vendor for the product. 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: COUNT(product_code) 5 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: COUNT(*) 5 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: COUNT(product_line) 5 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: COUNT(DISTINCT product_line) 2 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: COUNT(product_code) 3 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: COUNT(product_vendor) 4 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: product_lineCOUNT(product_code) Motorcycles3 Classic Cars2 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: product_lineCOUNT(product_code) Motorcycles3 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: product_lineCOUNT(product_code) Classic Cars2 Motorcycles3 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: product_codeproduct_nameproduct_lineproduct_vendorbuy_price S10_1678Harley Davidson ChopperMotorcyclesMLD4881 S10_2016Moto Guzzi 1100iMotorcyclesHMC6899 S10_4698Harley Davidson Eagle DragMotorcyclesRSD9102 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! Tags: sql learn sql aggregate functions