COUNT SQL Origin: Data OSI layer 7 · Application Last updated 18 Nov 2026

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

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

Exact message you may see COUNT(*) vs COUNT(column) different resultsCOUNT ignores NULL valuesAVG ignores NULLaggregate function eliminated NULL valueWarning: Null value is eliminated by an aggregate or other SET operation
Seen on: SQL SQL Server PostgreSQL MySQL

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?

🔧 Still not fixed?

Many errors look alike. If the steps above didn’t solve it, one of these is probably what you’re facing:

Where this error sits

Origin: Data. Errors that are often confused with or caused by this one (click to open):

= NULL — WHERE col = NULL returns no rows (use IS NULL) (SQL)= NULL515 — SQL Server Msg 515: Cannot insert the value NULL into column 'X', table 'Y'; column does not allow nulls (SQL)5158120 — SQL Server Msg 8120: Column is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause (SQL)8120aggregate in WHERE — ERROR: aggregate functions are not allowed in WHERE (use HAVING) / MySQL 1111 Invalid use of group function (SQL)aggregate in WHEREcartesian — Query returns far too many rows: accidental Cartesian product (missing JOIN condition) (SQL)cartesianCASE — CASE expression returns mixed types: conversion failed / types cannot be matched (SQL)CASECOMMIT — Changes disappear: transaction never committed (autocommit off, missing COMMIT) (SQL)COMMITdate format — Dates mixed up: dd/mm/yyyy vs mm/dd/yyyy, ISO 8601 recommended (SQL)date formatCOUNT

Explore the map of this error Full error map →

🧠 Still stuck? Analyze your error

Paste the full message, response headers or stack trace — we'll detect the platform and point to the most likely cause.

💬 What worked for others

No tips yet — did something else fix this for you?

Share what worked for you

Tips are checked by an editor before they appear. Links are removed.