SQL Count()
COUNT(*) and COUNT(column) answer different questions. The first counts rows, while the second counts rows where that column is not NULL. This difference matters when reporting missing information.
Understand the idea
Our four customers do not all have an email or a known city. Counting those columns gives a completeness check. COUNT(DISTINCT city) counts different non-NULL cities rather than customers. A useful report names the result columns so a reader knows whether a number means people, values, or categories.
Where to try it
Run this example in the SQL playground. The lesson link loads the shop tables and sample rows before this query. Select Run when you are ready. Every run starts fresh, so your changes do not affect the website or a previous run.
SQLite sample data · MySQL / MariaDB sample data · See the sample tables
A worked example
SELECT COUNT(*) AS customers,
COUNT(email) AS recorded_emails,
COUNT(DISTINCT city) AS known_cities
FROM customers;
Read the result
The result is 4 customers, 3 recorded emails, and 2 known cities. Nadiad appears twice but counts once in the distinct city count. The missing city is not counted.
Try one change
Count orders for each customer later with a LEFT JOIN. Keep Noor in the result with a count of zero.