COUNT(*) vs COUNT(column)

basic

HR needs to understand bonus distribution. How many employees exist? How many received bonuses?

SELECT

COUNT(*) AS total_employees,

COUNT(bonus) AS employees_with_bonus,

COUNT(*) - COUNT(bonus) AS employees_without_bonus

FROM employees;

total_employees | employees_with_bonus | employees_without_bonus

4 | 2 | 2

COUNT(*) = 4 counts all rows. COUNT(bonus) = 2 counts only non-NULL bonus values. The difference shows employees without bonuses.

All

SUM and AVG with NULL

intermediate

Finance calculates bonus statistics. Why AVG doesn't treat NULL as zero matters for budget planning.

SELECT

SUM(bonus) AS total_bonus_paid,

AVG(bonus) AS avg_bonus_per_recipient,

AVG(COALESCE(bonus, 0)) AS avg_bonus_per_employee,

COUNT(bonus) AS bonus_recipients,

COUNT(*) AS total_employees

FROM employees;

total_bonus_paid | avg_bonus_per_recipient | avg_bonus_per_employee | bonus_recipients | total_employees

8000 | 4000 | 2000 | 2 | 4

AVG(bonus) = 4000 (8000 ÷ 2 recipients). AVG(COALESCE(bonus, 0)) = 2000 (8000 ÷ 4 employees). Different business questions.

All