Bird
Raised Fist0
SQLquery~5 mins

COUNT function behavior in SQL - Cheat Sheet & Quick Revision

Choose your learning style10 modes available
LearnWhyDeepVisualTryChallengeProjectRecallTime

Start learning this pattern below

Jump into concepts and practice - no test required

or
Recommended
Test this pattern10 questions across easy, medium, and hard to know if this pattern is strong
Recall & Review
beginner
What does the SQL COUNT(*) function do?
COUNT(*) counts all rows in a table, including rows with NULL values in any column.
Click to reveal answer
beginner
How does COUNT(column_name) differ from COUNT(*)?
COUNT(column_name) counts only rows where the specified column is NOT NULL, ignoring rows with NULL in that column.
Click to reveal answer
intermediate
What will COUNT(1) count in a SQL query?
COUNT(1) counts all rows just like COUNT(*), because 1 is a constant and not NULL, so it counts every row.
Click to reveal answer
beginner
If a column has NULL values, will COUNT(column) include those rows?
No, COUNT(column) excludes rows where the column value is NULL. Only non-NULL values are counted.
Click to reveal answer
intermediate
Can COUNT be used to count distinct values? How?
Yes, use COUNT(DISTINCT column_name) to count unique non-NULL values in that column.
Click to reveal answer
What does COUNT(*) return in a SQL query?
ANumber of NULL values in a column
BNumber of rows with non-NULL values in a specific column
CNumber of distinct values in a column
DNumber of all rows including those with NULLs
Which COUNT function counts only non-NULL values in a column?
ACOUNT(*)
BCOUNT(1)
CCOUNT(column_name)
DCOUNT(DISTINCT *)
What will COUNT(1) return compared to COUNT(*)?
ACounts only rows where column 1 is not NULL
BCounts all rows, same as COUNT(*)
CCounts distinct rows
DCounts only NULL rows
How to count unique non-NULL values in a column?
ACOUNT(DISTINCT column_name)
BCOUNT(column_name)
CCOUNT(*)
DCOUNT(NULL)
If a column has NULL values, what does COUNT(column) do with those rows?
AIgnores them
BCounts them
CCounts them as zero
DReturns an error
Explain how COUNT(*) and COUNT(column_name) behave differently when counting rows.
Think about whether NULL values are included or excluded.
You got /3 concepts.
    Describe how to count unique values in a column using COUNT.
    Focus on the DISTINCT keyword.
    You got /3 concepts.

      Practice

      (1/5)
      1. What does the SQL function COUNT(*) do when used in a query?
      easy
      A. Counts only rows with NULL values
      B. Counts only rows where all columns are NOT NULL
      C. Counts all rows in the table, including those with NULL values
      D. Counts only distinct values in a column

      Solution

      1. Step 1: Understand COUNT(*) behavior

        The COUNT(*) function counts every row in the table regardless of NULL values in any column.

      2. Step 2: Compare with other COUNT variants

        Unlike COUNT(column), which skips NULLs, COUNT(*) includes all rows.

      3. Final Answer:

        Counts all rows in the table, including those with NULL values -> Option C

      4. Quick Check:

        COUNT(*) counts all rows [OK]
      Hint: COUNT(*) counts every row, NULL or not [OK]
      Common Mistakes:
      • Thinking COUNT(*) skips NULL rows
      • Confusing COUNT(*) with COUNT(column)
      • Assuming COUNT(*) counts distinct values
      2. Which of the following is the correct syntax to count only non-NULL values in the column age from the table persons?
      easy
      A. SELECT COUNT(*) FROM persons;
      B. SELECT COUNT(age) FROM persons;
      C. SELECT COUNT(DISTINCT age) FROM persons;
      D. SELECT COUNT(age IS NOT NULL) FROM persons;

      Solution

      1. Step 1: Identify how to count non-NULL values

        COUNT(column) counts only non-NULL values in that column.

      2. Step 2: Check syntax correctness

        SELECT COUNT(age) FROM persons; correctly counts non-NULL age values.

      3. Final Answer:

        SELECT COUNT(age) FROM persons; -> Option B

      4. Quick Check:

        COUNT(column) counts non-NULL values [OK]
      Hint: Use COUNT(column) to count non-NULL values [OK]
      Common Mistakes:
      • Using COUNT(*) to count non-NULL values
      • Using WHERE clause unnecessarily
      • Using COUNT with boolean expressions
      3. Given the table employees with the column department containing values: ['HR', 'IT', NULL, 'IT', 'HR', 'Finance', NULL], what is the result of the query SELECT COUNT(DISTINCT department) FROM employees;?
      medium
      A. 3
      B. 5
      C. 7
      D. 4

      Solution

      1. Step 1: Identify distinct non-NULL values in department

        The distinct non-NULL values are 'HR', 'IT', and 'Finance'. That's 3 unique values.

      2. Step 2: Understand COUNT(DISTINCT) behavior

        COUNT(DISTINCT column) counts unique non-NULL values only, so NULLs are excluded.

      3. Step 3: Count distinct values

        There are 3 distinct non-NULL values, so the count is 3.

      4. Final Answer:

        3 -> Option A

      5. Quick Check:

        COUNT(DISTINCT) excludes NULLs [OK]
      Hint: COUNT(DISTINCT) counts unique non-NULL values only [OK]
      Common Mistakes:
      • Including NULL as a distinct value
      • Counting total rows instead of distinct
      • Confusing COUNT(*) with COUNT(DISTINCT)
      4. Consider the query: SELECT COUNT(employee_id) FROM staff; but it returns 0 even though the table has rows. What is the most likely reason?
      medium
      A. The column employee_id contains only NULL values
      B. The table staff is empty
      C. COUNT(employee_id) counts all rows including NULLs
      D. Syntax error in the query

      Solution

      1. Step 1: Understand COUNT(column) behavior

        COUNT(column) counts only non-NULL values in that column.

      2. Step 2: Analyze why count is zero

        If all employee_id values are NULL, COUNT returns 0 even if rows exist.

      3. Final Answer:

        The column employee_id contains only NULL values -> Option A

      4. Quick Check:

        COUNT(column) excludes NULLs [OK]
      Hint: COUNT(column) returns 0 if all values are NULL [OK]
      Common Mistakes:
      • Assuming COUNT(column) counts all rows
      • Thinking table is empty without checking data
      • Assuming syntax error without checking query
      5. You want to find how many unique customers placed orders, but some orders have NULL customer IDs. Which query correctly counts unique customers excluding NULLs?
      hard
      A. SELECT COUNT(customer_id) FROM orders;
      B. SELECT COUNT(*) FROM orders WHERE customer_id IS NOT NULL;
      C. SELECT COUNT(DISTINCT *) FROM orders;
      D. SELECT COUNT(DISTINCT customer_id) FROM orders;

      Solution

      1. Step 1: Understand requirement for unique customers excluding NULLs

        We need to count distinct customer IDs ignoring NULLs.

      2. Step 2: Analyze options

        SELECT COUNT(DISTINCT customer_id) FROM orders; uses COUNT(DISTINCT customer_id), which counts unique non-NULL values correctly.

      3. Step 3: Eliminate incorrect options

        SELECT COUNT(customer_id) FROM orders; counts all non-NULL customer IDs including duplicates. SELECT COUNT(*) FROM orders WHERE customer_id IS NOT NULL; counts rows with non-NULL customer IDs but not distinct. SELECT COUNT(DISTINCT *) FROM orders; is invalid syntax.

      4. Final Answer:

        SELECT COUNT(DISTINCT customer_id) FROM orders; -> Option D

      5. Quick Check:

        COUNT(DISTINCT column) counts unique non-NULL values [OK]
      Hint: Use COUNT(DISTINCT column) for unique non-NULL counts [OK]
      Common Mistakes:
      • Using COUNT(column) to count unique values
      • Using COUNT(*) with WHERE instead of DISTINCT
      • Using invalid syntax like COUNT(DISTINCT *)