Skip to main content
HomeTutorialsSQL

COUNT() SQL FUNCTION

COUNT() lets you count the number of rows that match certain conditions. Learn how to use it in this tutorial.
Oct 2022  · 3 min read

What is the COUNT() function?

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

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.

COUNT() examples

Example 1: 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

Example 2: 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

Example 3: 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

Example 4: 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

Example 5: 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. 

See also

Learn more about SQL

Popular SQL Courses

Certification available

Introduction to SQL

BeginnerSkill Level
2 hr
466.2K
Learn how to create and query relational databases using SQL in just two hours.
See DetailsRight Arrow
Start Course
Certification available

Intermediate SQL

BeginnerSkill Level
4 hr
151.8K
Accompanied at every step with hands-on practice queries, this course teaches you everything you need to know to analyze data using your own SQL code today!
See MoreRight Arrow
Related

The 6 Best Business Analyst Certifications: Your Path to Becoming Certified

Explore the top business analyst certifications to enhance your career. Learn about the benefits, preparation tips, and how DataCamp can support you.
Matt Crabtree's photo

Matt Crabtree

15 min

Top 31 Business Analyst Interview Questions and Answers For All Levels

Explore common business analyst interview questions and their answers for all experience levels.
Austin Chia's photo

Austin Chia

18 min

SQL vs NoSQL Databases: Key Differences and Practical Insights

Discover how to decide between SQL and NoSQL databases in data science and application development. Learn their strengths, use cases, and industry applications.

Kevin Babitz

15 min

The Top 8 Business Analyst Skills for 2024

Explore the top technical and soft business analyst skills needed to succeed and how you can best showcase them in your portfolio and on your resume.
Joleen Bothma's photo

Joleen Bothma

11 min

MySQL Basics Cheat Sheet

With this MySQL basics cheat sheet, you'll have a handy reference guide to basic querying tables, filtering data, and aggregating data
Richie Cotton's photo

Richie Cotton

6 min

PostgreSQL Basics Cheat Sheet

With this PostgreSQL cheat sheet, you'll have a handy reference guide to basic querying tables, filtering data, and aggregating data
Richie Cotton's photo

Richie Cotton

6 min

See MoreSee More