Skip to content
Quantoros

What is the difference between COUNT() and COUNT(columnname)?

SQLLevel: FoundationAggregations Basics

What is the difference between COUNT(*) and COUNT(column_name)?

Hints
  • Define one output row and the source-table keys.
  • Check duplicates, NULLs and the order of filtering and aggregation.

Answer

COUNT(*) counts all rows, including rows where the column values are NULL. COUNT(column_name) counts only rows where that column is NOT NULL.

CREATE TABLE orders (
    order_id   INT,
    coupon_code VARCHAR(20)  -- NULL if no coupon used
);

SELECT
    COUNT(*)           AS total_orders,       -- counts all rows
    COUNT(coupon_code) AS orders_with_coupon  -- counts only non-NULL coupon_code
FROM orders;

This is a trap question. If the column has no NULLs, they return the same thing. The difference shows up when there are NULLs.

Source and licence: SQL Interview Questions — Arda Kocadoru · MIT

Related questions