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