Chapter 24: PostgreSQL COUNT Function

Part 1: What is the COUNT Function?

The COUNT function returns the number of rows that match a specified condition. It’s an aggregate function that comes in several flavors:

• COUNT(*) – Counts all rows in a table or group

• COUNT(column) – Counts non-NULL values in a specific column

• COUNT(DISTINCT column) – Counts unique non-NULL values

The Basic Syntax

Part 2: Setting Up Our Example Data

Let’s create a rich dataset to explore all the ways COUNT can be used:

Part 3: The Three Flavors of COUNT

1. COUNT(*) – Count All Rows

This counts every row in the table, regardless of NULL values:

Result:

Result:

Important: COUNT(*) includes rows even if all columns are NULL. It’s counting rows, not values.

2. COUNT(column) – Count Non-NULL Values

This counts only rows where the specified column is NOT NULL:

The real power shows when you have NULL values:

Result:

3. COUNT(DISTINCT column) – Count Unique Values

This counts the number of unique, non-NULL values in a column:

Result:

Result:

(All our customers are in Texas for this example)

Part 4: COUNT with WHERE Clause

Filter rows before counting:

1. Count Active Customers

Or more simply:

2. Count by Status

3. Count with Multiple Conditions

Part 5: COUNT with GROUP BY

This is where COUNT becomes a powerful analytical tool – counting within groups.

1. Count by Category

Result:

2. Multiple Grouping Columns

Result (partial):

3. Count with Joins

Note: The LEFT JOIN ensures products with zero orders still appear.

Part 6: COUNT with HAVING

Filter groups based on their count:

1. Find Popular Products

2. Find Active Categories

3. Find High-Value Customers

Part 7: Conditional Counting with FILTER

PostgreSQL offers a cleaner syntax for conditional counts:

This is often more readable than CASE statements:

Complex FILTER Conditions

Part 8: COUNT with Window Functions

Window functions let you count while keeping all rows:

1. Running Count

2. Total Count in Each Row

3. Percentage of Total

Part 9: Advanced COUNT Techniques

1. Counting with Multiple Conditions

2. Counting Missing Data

3. Rolling Counts Over Time

4. Count with CUBE and ROLLUP

Part 10: Real-World Examples

Example 1: Executive Dashboard

Example 2: Customer Segmentation

Example 3: Inventory Analysis

Example 4: Order Fulfillment Analysis

Example 5: Review Analysis

Part 11: Performance Considerations

1. Indexes for COUNT

Different COUNT queries benefit from different indexes:

2. Understanding COUNT Performance

3. Approximate Counts for Large Tables

For very large tables, exact counts can be slow:

Part 12: Common Mistakes and How to Avoid Them

Mistake 1: COUNT(*) vs COUNT(column) Confusion

Mistake 2: Forgetting that COUNT(DISTINCT) ignores NULLs

Mistake 3: Using COUNT in WHERE instead of HAVING

Mistake 4: Not Handling NULLs in Conditional Counts

Mistake 5: Counting Without Considering Joins

Summary: The COUNT Philosophy

The COUNT function is your fundamental tool for understanding volume and quantity in your database:

1. COUNT(*) – “How many rows are there?”

2. COUNT(column) – “How many non-NULL values in this column?”

3. COUNT(DISTINCT) – “How many unique values exist?”

Master these patterns:

• Simple totals with COUNT(*)

• Conditional counts with FILTER or CASE

• Grouped counts with GROUP BY

• Filtered groups with HAVING

• Running counts with window functions

• Data quality checks with COUNT and NULL handling

Remember:

• COUNT(*) includes everything, NULLs included

• COUNT(column) excludes NULLs

• COUNT(DISTINCT) counts unique non-NULL values

• Indexes can dramatically speed up COUNT queries

• For very large tables, consider approximate counts

Key applications:

• Business metrics (“How many customers signed up this month?”)

• Inventory management (“How many products are low in stock?”)

• Quality control (“How many missing email addresses?”)

• Customer analysis (“How many orders per customer?”)

• Performance monitoring (“How many orders are pending?”)

The COUNT function transforms raw data into actionable business intelligence. It answers the most fundamental question in data analysis: “How many?” Master COUNT, and you’ll always know the size and shape of your data landscape.

Would you like me to elaborate on any specific aspect of COUNT, such as more complex counting scenarios, performance optimization for large tables, or integration with other aggregate functions?