# COUNT(column) ignores NULLs: counts differ from COUNT(*)

**Code:** `COUNT` · **Category:** SQL · **Seen on:** SQL, SQL Server, PostgreSQL, MySQL

`COUNT(col)` counts only non-NULL values, while `COUNT(*)` counts rows, so totals don't match.

## Meaning

Aggregates (SUM, AVG, COUNT(col)) skip NULLs, and `AVG` is computed over non-NULL rows only. Use `COUNT(*)` for rows, `COUNT(DISTINCT col)` for unique non-null values, and `COALESCE` when NULL should count as 0.

## Common causes

- Nullable column inside COUNT
- AVG ignoring NULL rows
- LEFT JOIN producing NULLs in the counted column
- DISTINCT confusion
- Expecting NULL to be zero

## Quick fix

1. Use `COUNT(*)` to count rows
2. `COALESCE(col, 0)` before aggregating
3. Check the nullable columns in the query

## Detailed fix by platform

### SQL

1. `SELECT COUNT(*) AS rows_, COUNT(email) AS with_email, AVG(COALESCE(score, 0)) AS avg_all FROM users;`

## How to diagnose

1. **Column** — Nullable?
2. **COUNT** — (*) or (col)?
3. **AVG** — Over non-NULL only?

---
Source: [WhatIsMyError](https://whatismyerror.com/errors/sql/count-star-vs-count-column-null)
