Exploratory data analysis (EDA) in SQL means asking a table a few blunt questions before you trust it: how big is it, what is missing, what values does each column really hold, and is anything duplicated or absurd? You run these checks inside MySQL, where the data already lives, before you export a single row to Excel, Power BI or pandas.
Below is one small orders table with problems planted in it on purpose, and the six queries that find them. Every result was produced on MySQL 8.0.
The sample table
CREATE TABLE orders (
order_id INT PRIMARY KEY,
customer_id INT,
city VARCHAR(20),
order_date DATE,
amount DECIMAL(10,2),
status VARCHAR(12)
);
INSERT INTO orders VALUES
(101, 1, 'Delhi', '2026-08-01', 1200.00, 'delivered'),
(102, 2, 'Pune', '2026-08-01', 850.00, 'delivered'),
(103, 3, 'delhi', '2026-08-02', 2300.00, 'returned'),
(104, 1, 'Delhi', '2026-08-03', 1200.00, 'delivered'),
(105, 4, NULL, '2026-08-03', 640.00, 'delivered'),
(106, 5, 'Mumbai', '2026-08-04', 98000.00, 'delivered'),
(107, 2, 'Pune', '2026-08-05', NULL, 'cancelled'),
(108, 6, 'Mumbai', '2026-08-05', 1500.00, 'delivered'),
(109, 7, 'Pune ', '2026-08-06', 920.00, 'delivered'),
(110, 1, 'Delhi', '2026-08-03', 1200.00, 'delivered');
Step 1: size and date range
SELECT COUNT(*) AS rows_total,
MIN(order_date) AS first_day,
MAX(order_date) AS last_day,
COUNT(DISTINCT customer_id) AS customers
FROM orders;
| rows_total | first_day | last_day | customers |
|---|---|---|---|
| 10 | 2026-08-01 | 2026-08-06 | 7 |
Compare this with what you were told. If someone said "a month of orders" and the range is six days, stop and ask before analysing anything.
Step 2: a NULL profile
SELECT SUM(city IS NULL) AS null_city,
SUM(amount IS NULL) AS null_amount,
SUM(status IS NULL) AS null_status
FROM orders;
| null_city | null_amount | null_status |
|---|---|---|
| 1 | 1 | 0 |
In MySQL, city IS NULL returns 1 or 0, so SUM() counts the gaps. Remember that AVG(amount) silently skips the NULL row. The article on handling NULL values in MySQL explains when that matters.
Step 3: what a text column really contains
SELECT city, COUNT(*) AS n FROM orders GROUP BY city ORDER BY n DESC;
| city | n |
|---|---|
| Delhi | 4 |
| Pune | 2 |
| Mumbai | 2 |
| NULL | 1 |
| Pune | 1 |
Two surprises. "Delhi" and "delhi" were grouped together because the default collation (utf8mb4_0900_ai_ci) ignores case. But "Pune " with a trailing space got its own group, because that collation does not pad spaces. Clean the value before grouping:
SELECT LOWER(TRIM(city)) AS city_clean, COUNT(*) AS n
FROM orders GROUP BY city_clean ORDER BY n DESC;
| city_clean | n |
|---|---|
| delhi | 4 |
| pune | 3 |
| mumbai | 2 |
| NULL | 1 |
Step 4: numeric spread and outliers
SELECT MIN(amount) AS min_amt, MAX(amount) AS max_amt,
ROUND(AVG(amount), 2) AS avg_amt,
ROUND(STDDEV_POP(amount), 2) AS sd
FROM orders;
| min_amt | max_amt | avg_amt | sd |
|---|---|---|---|
| 640.00 | 98000.00 | 11978.89 | 30416.36 |
An average of about ₹12,000 when most orders are near ₹1,000 is a warning sign. MySQL has no MEDIAN() function, but a window function gives you one:
WITH r AS (
SELECT amount,
ROW_NUMBER() OVER (ORDER BY amount) AS rn,
COUNT(*) OVER () AS n
FROM orders WHERE amount IS NOT NULL
)
SELECT AVG(amount) AS median_amt
FROM r WHERE rn IN (FLOOR((n + 1) / 2), CEIL((n + 1) / 2));
| median_amt |
|---|
| 1200.000000 |
The median is ₹1,200. Order 106 (₹98,000) pulls the mean up on its own. Whether it is a bulk order or a typo is a question for the business, not for SQL. Window functions are explained in the MySQL window functions tutorial.
Step 5: duplicates on the natural key
SELECT customer_id, order_date, amount, COUNT(*) AS copies
FROM orders
GROUP BY customer_id, order_date, amount
HAVING COUNT(*) > 1;
| customer_id | order_date | amount | copies |
|---|---|---|---|
| 1 | 2026-08-03 | 1200.00 | 2 |
Orders 104 and 110 have different IDs but the same customer, day and amount. That may be a double submit from the app. A unique order_id does not prove the rows are unique events.
Step 6: category shares
SELECT status, COUNT(*) AS n,
ROUND(100 * COUNT(*) / SUM(COUNT(*)) OVER (), 1) AS pct
FROM orders GROUP BY status ORDER BY n DESC;
| status | n | pct |
|---|---|---|
| delivered | 8 | 80.0 |
| returned | 1 | 10.0 |
| cancelled | 1 | 10.0 |
Now you know revenue numbers should probably exclude cancelled and returned orders. Decide that rule in writing before you build a report.
Pitfalls
- Profiling a sample instead of the table.
LIMIT 100shows the first rows the engine finds, not a representative sample. - Trusting collation. Case and trailing spaces behave differently depending on the column's collation. Check it with
SHOW FULL COLUMNS FROM orders;. - Running heavy profiling on production. Use a replica or a copy for big tables.
- Fixing data silently. Write down every cleaning rule (TRIM, LOWER, excluded statuses) so others can repeat it.
After EDA you usually summarise with aggregate functions and GROUP BY. To see the same thinking applied to business questions, read MySQL for data analysis.