BA Prince the B.A. Subscribe

Home / SQL for Business Analysts

Profile a New Table in SQL in 5 Queries

Quick answer

Run 5 short queries: COUNT(*) for the number of rows, COUNT(*) minus COUNT(column) for empty values, MIN and MAX for the range, COUNT(DISTINCT column) for the number of different values, and GROUP BY with COUNT(*) to see how rows spread across groups.

SELECT COUNT(*) - COUNT(email) AS no_email,
  COUNT(*) - COUNT(city) AS no_city
FROM customers;

Watch on YouTube Subscribe

Five quick SQL queries tell you what is inside a table you have never seen: how many rows, how many empty values, the range of dates, how many distinct values, and how the rows spread across groups. Shown on the customers table of a sample online shop.

Step by step

A new table

Know your data. Before you build a report on a table you have never seen, profile it. 5 short SQL queries show what is really inside. Here we profile the customers table of a sample online shop.
The customers table. The customers table has 7 columns: an ID, names, email, city, region and signup date.

Row count

How many rows. Query 1 counts the rows. COUNT(*) counts every row in the table, and AS row_count names the result column. It returns 40 customers. Write that number down, because you compare every later check against it.
```
SELECT COUNT(*) AS row_count
FROM customers;
```

Empty values

Empty values. Query 2 counts empty values. COUNT(email) skips NULLs, so COUNT(*) minus COUNT(email) gives the customers with no email. The same sum on city gives no_city. The result: 2 customers have no email and 1 has no city. Good to know before anyone plans an email campaign.
```
SELECT COUNT(*) - COUNT(email) AS no_email,
  COUNT(*) - COUNT(city) AS no_city
FROM customers;
```

Ranges

The range. Query 3 checks the range. MIN and MAX return the smallest and biggest value. On signup_date, that is the first and the last signup. The first signup is in January 2024 and the last is in June 2025. There are no odd dates far in the past or the future.
```
SELECT MIN(signup_date) AS first_signup,
  MAX(signup_date) AS last_signup
FROM customers;
```

Distinct values

Distinct values. Query 4 counts different values. COUNT(DISTINCT city) counts each city once, and COUNT(DISTINCT email) does the same for emails. There are 12 cities. But there are only 37 different emails out of the 38 that are filled in (40 rows minus 2 empty). So one email is used twice. That is a duplicate to chase.
```
SELECT COUNT(DISTINCT city) AS cities,
  COUNT(DISTINCT email) AS emails
FROM customers;
```

Spread

The spread. Query 5 shows the spread. GROUP BY region puts the rows into one group per region, COUNT(*) counts each group, and ORDER BY how_many DESC puts the biggest group first. There are 4 regions, and they are fairly balanced. East has 11 customers, and South has 9.
```
SELECT region, COUNT(*) AS how_many
FROM customers
GROUP BY region
ORDER BY how_many DESC;
```

Recap

Five profile queries. To recap: count the rows, count the NULLs, check the MIN and MAX, count the distinct values, and group to see the spread. After those 5 queries you know what the table holds.

Common mistakes

Using COUNT(column) to count rows. COUNT(email) skips NULLs, so here it returns 38, not 40. Use COUNT(*) for the row count, and keep COUNT(column) for counting filled-in values.
Treating blank text as filled in. In most databases (not Oracle, which treats '' as NULL), an empty string is not NULL, so COUNT(email) counts it as an email. Also run a query with WHERE email = '' (or TRIM(email) = '') to catch blanks that the NULL check misses.
Comparing COUNT(DISTINCT) with COUNT(*). COUNT(DISTINCT email) ignores NULLs, so compare it with COUNT(email) (38), not COUNT(*) (40). Comparing 37 with 40 would wrongly suggest 3 duplicates when there is only 1.
Missing duplicates that differ only by case or spaces. Depending on the database and its collation settings, Ann@shop.com and ann@shop.com can count as 2 different emails. Run COUNT(DISTINCT LOWER(TRIM(email))) to catch these.

When a business analyst uses this

Before a data migration workshop, a business analyst runs these 5 queries on the source customers table. The 2 missing emails and the 1 duplicate email go into the RAID log as data quality issues, each with an owner, so they are fixed before the data moves and before sign-off on the migration.

Questions people ask

Does COUNT(*) count NULL values?

Yes. COUNT(*) counts every row, even rows full of NULLs. COUNT(column) counts only the rows where that column is not NULL.

Does COUNT(DISTINCT) include NULL?

No. COUNT(DISTINCT column) skips NULLs, so 2 empty emails are not counted as a value. Compare it with COUNT(column), not COUNT(*), when you check for duplicates.

How do I find duplicate values in a SQL column?

If COUNT(DISTINCT email) is lower than COUNT(email), there are duplicates. To see which values repeat, filter out NULLs with WHERE email IS NOT NULL, group by the column, and keep the groups with HAVING COUNT(*) > 1.

More in SQL for Business Analysts

COUNT vs COUNT DISTINCT in SQL

SUM, AVG, MIN, MAX in SQL: Summary Stats for a Report

SQL CASE WHEN: If-Then Logic in a Query

SQL CTE (WITH): Make Long Queries Readable, Step by Step