beginner

COUNT Function

By DataPlayArena Technical Review BoardUpdated 2026-07-235 min readRuntime Verified

Overview

The COUNT aggregate function counts the number of occurrences or rows within a table or partition.

Learning Objectives

  • Distinguish between COUNT(*), COUNT(column), and COUNT(1).
  • Use COUNT(DISTINCT col) to calculate unique occurrences.
  • Group counts by attributes.

Detailed Concept Explanation

  • COUNT(*): Counts all rows matching the query bounds, including rows with NULL or duplicate values.
  • COUNT(1): Under the hood, this behaves exactly the same as COUNT(*) in modern database engines.
  • COUNT(column): Counts only rows where the specified column is not NULL.
  • COUNT(DISTINCT column): Counts only the unique, non-null values in a column.

Code Examples

SQL

sql
-- Compare COUNT variations
SELECT 
  COUNT(*) AS total_rows,
  COUNT(phone) AS rows_with_phone, -- Skips NULL phone rows
  COUNT(DISTINCT country) AS unique_countries -- Skips NULLs and deduplicates
FROM customers;

Best Practices

  • Use COUNT(*) for Row Counts: Avoid specifying specific columns inside COUNT() unless you explicitly want to ignore NULL occurrences. Specifying COUNT(*) is highly optimized by database query plans.

Interview Perspective

Note

Interview Question: What is the difference between COUNT(*) and COUNT(column)?

COUNT(*) returns the total row count, including rows containing NULL values. COUNT(column) evaluates the specified column, ignoring any rows where that column evaluates to NULL.


Interactive Challenges

Challenge 1: Count Unique Domains

Calculate the total count of unique department IDs (alias 'unique_depts') from the 'employees' table.


Summary

The COUNT function tallies active observations, with options to skip nulls or filter for unique values using DISTINCT.

DPA

DataPlayArena Technical Editorial Board

Curated & maintained by enterprise data platform engineers with 10+ years in distributed systems.

Runtime Verification Standards: Every code example, query, and architecture diagram in this tutorial is compiled and verified against real runtimes (Apache Spark 3.5, Python 3.11, PostgreSQL, and DuckDB). If you spot any errata or want to suggest improvements, please submit feedback.