How do you count occurrences in a column in SQL?

Group by the column and count. That gives one row per distinct value with its frequency.

SELECT status, COUNT(*) AS occurrences
FROM   orders
GROUP  BY status
ORDER  BY occurrences DESC;

The COUNT variants differ, and the difference is the interview question

So on a column with 100 rows of which 10 are NULL, COUNT(*) returns 100 and COUNT(status) returns 90. Subtracting the two is the idiomatic way to count NULLs:

SELECT COUNT(*) - COUNT(status) AS null_count FROM orders;

Note also that GROUP BY treats all NULLs as a single group, even though NULL does not equal NULL in a comparison — a deliberate exception in the standard.

Counting one specific value

SELECT COUNT(*) FROM orders WHERE status = 'shipped';

Counting several conditions in one pass

Rather than running separate queries, count conditionally. Because COUNT ignores NULLs, a CASE with no ELSE acts as a filter:

SELECT COUNT(CASE WHEN status = 'shipped'   THEN 1 END) AS shipped,
       COUNT(CASE WHEN status = 'cancelled' THEN 1 END) AS cancelled
FROM   orders;

PostgreSQL and SQLite support the cleaner COUNT(*) FILTER (WHERE status = 'shipped'). SUM(CASE WHEN ... THEN 1 ELSE 0 END) works everywhere and is equivalent.

Performance

An index on the grouped column lets the engine use an index scan and, in some engines, skip the sort. COUNT(DISTINCT ...) is markedly more expensive than COUNT(*) because it must deduplicate; at large scale, approximate functions such as APPROX_COUNT_DISTINCT or HyperLogLog trade a small error for a large speedup.

In the interview, also mention that COUNT(1) and COUNT(*) are identical in every major engine — the supposed performance difference is a myth.