Skip to main content

COUNT() SQL FUNCTION

COUNT() lets you count the number of rows that match certain conditions. Learn how to use it in this tutorial.
Updated Dec 12, 2024  · 3 min read

It goes without saying that counting rows or values is an important part of data analysis. So it's no surprise that SQL has its own function to help. Whether you're identifying duplicates, calculating group totals, or filtering data, the COUNT() function is here to help.

In this article, I'll show you the many ways in which COUNT() is useful, from its basic syntax to more interesting use cases with GROUP BY and HAVING. Consider also enrolling in our SQL Associate Certification which is a great way both to learn and help your resume. 

What is the COUNT() Function in SQL?

The COUNT() function returns the number of rows that matches a criterion.

SQL COUNT() Syntax

The basic syntax of COUNT() is as follows.

SELECT COUNT(column_name)
FROM table_name;

Variations of the syntax achieve different goals.

  • The COUNT(*) syntax allows us to count the number of rows in a table

  • The COUNT(DISTINCT column) syntax allows us to count the number of distinct values in a column 

  • The COUNT(CASE WHEN condition THEN column END) syntax allows us to count the number of values that fulfill conditions.

COUNT(), used with GROUP BY, is useful for counting the number of rows that belong to each group.

COUNT(), used with HAVING, is useful for filtering groups according to the number of rows they have.

We will illustrate these with the examples below.

Associate Data Engineer in SQL

Gain practical knowledge in ETL, SQL, and data warehousing for data engineering.
Explore Track

SQL COUNT() Function Examples

Run and edit the code from this tutorial online

Run code

Counting the number of rows with COUNT(*)

The table called products contains all the products a company sells. The COUNT(*) clause allows us to calculate the number of rows in the table. 

SELECT
    COUNT(*) AS number_of_rows
FROM products

number_of_rows

321

Counting unique values with COUNT(DISTINCT …)

The COUNT(DISTINCT column) syntax allows us to count the number of unique values in a column.

For example, each product has an associated brand in the products table. We can count the number of unique products and brands in the table.

SELECT
    COUNT(DISTINCT product_id) AS unique_product_count,
    COUNT(DISTINCT brand_id) AS unique_brand_count
FROM products

unique_product_count

unique_brand_count

321

9

SQL Upskilling for Beginners

Gain the SQL skills to interact with and query your data.
Start Learning for Free

Count rows that match a condition using COUNT() with CASE WHEN 

The COUNT(CASE WHEN condition THEN column END) syntax allows us to calculate the number of rows that match a condition.

For example, in the products table, each product has a list_price. We can calculate how many products are “expensive” (having a list price of more than $500) or otherwise.

SELECT
    COUNT(CASE WHEN list_price >= 500 THEN product_id END) AS expensive_product_count,
   COUNT(CASE WHEN list_price < 500 THEN product_id END) AS cheap_product_count
FROM products

expensive_product_count

cheap_product_count

213

108

Count rows in groups using COUNT() with GROUP BY

COUNT() can be used with GROUP BY to find the number of rows in each group.

For example, the product table contains bicycle models from 2016 to 2019. To find the distribution of the bicycle, we can use COUNT(product_id) and GROUP BY model_year to count the number of products from each year. 

SELECT
    model_year,
    COUNT(product_id) AS product_count
FROM products
GROUP BY model_year

model_year

product_count

2016

26

2017

85

2018

204

2019

6

Filter for groups using COUNT() with GROUP BY and HAVING

In example 4, we see that COUNT() can be used with GROUP BY. We can use the HAVING statement to filter for groups using the number of rows in that group.

For example, to find the number of years that have less than 50 products, we can use the following syntax. 

SELECT
   model_year
FROM products
GROUP BY model_year
HAVING COUNT(product_id) < 50

year_with_less_than_50_pdt

2016

2019

Technical Requirements

COUNT() is a function that is available to all versions of modern SQL. DataCamp offers tailored courses to help you master SQL within the context of your chosen database system. Here are some options to get started. I would think Oracle would be the most different since it has some specific functions and additional features. 

Learn More about SQL

Keep learning SQL with DataCamp. I've written a few other tutorials to get you started. I hope you find them helpful.

Also, consider our SQL Associate Certification, which I think looks great on your CV or resume.

Become SQL Certified

Prove your SQL skills are job-ready with a certification.
Boost My Career
Photo of Travis Tang
Author
Travis Tang
LinkedIn

A data scientist at Tiktok and a Master's student at GeorgiaTech, I have an innate passion for data science, I've forged a unique career path that intertwines tech, risk, and continuous self-improvement. My tenure as a Data Scientist at Merchant Platform saw me mitigating risks and combating fraud using complex data models, saving thousands of dollars weekly. I've honed my craft by dedicating countless hours to self-learning, acquiring certificates from renowned institutions, and becoming proficient in topics such as Deep Learning, TensorFlow, and Social Network Analysis. On DataCamp and other educational platforms, I use this wealth of knowledge to inspire and educate others, forging new paths in the field of data science and proving that with determination and the right data, any risk can be turned into an opportunity.

SQL COUNT() FAQs

Can COUNT() count only specific rows?

Yes, you can use a WHERE clause with COUNT() to count only rows that meet specific criteria. For example:

SELECT COUNT(*) FROM employees WHERE department = 'Sales';

What is the difference between COUNT(*) and COUNT(column_name)?

COUNT(*) counts all rows in a table, including rows with NULL values. COUNT(column_name) counts only the rows where the specified column is not NULL.

Can I count distinct values using COUNT()?

Yes, you can count unique values in a column by combining COUNT() with the DISTINCT keyword:

SELECT COUNT(DISTINCT department) FROM employees;

How does COUNT() handle duplicate rows?

By default, COUNT(*) counts all rows, including duplicates. To count only unique rows, use COUNT(DISTINCT column_name).

Topics
SQL
Data Analysis

Popular SQL Courses

course

Introduction to SQL

2 hr
929.3K
Learn how to create and query relational databases using SQL in just two hours.
See DetailsRight Arrow
Start Course
See MoreRight Arrow
Related

tutorial

INSERT INTO SQL FUNCTION

INSERT INTO lets you add data to your tables. Learn how to use it in this tutorial.
Travis Tang 's photo

Travis Tang

3 min

tutorial

SQL SUM() Function Explained

Discover the power of the SQL SUM() function for data aggregation. Learn how to implement rolling sums, cumulative sums, and sum multiple columns effectively.
Allan Ouko's photo

Allan Ouko

8 min

tutorial

Aggregate Functions in SQL

Learn how to use aggregate functions for summarizing results and gaining useful insights about data in SQL.
Sayak Paul's photo

Sayak Paul

9 min

tutorial

FORMAT() SQL FUNCTION

FORMAT() is one of the most commonly used functions in SQL. Learn its main applications in this tutorial.
Travis Tang 's photo

Travis Tang

3 min

tutorial

COALESCE SQL Function

COALESCE() is one of the handiest functions in SQL. Read this tutorial to learn how to master it.
Travis Tang 's photo

Travis Tang

4 min

tutorial

ROW_NUMBER SQL Function: How to Display Row Numbers

The ROW_NUMBER() SQL function assigns sequential integers to rows within a result set, optionally partitioning the data and ordering the rows within each partition.

Kevin Babitz

6 min

See MoreSee More