#1 India's Top IT Training Institute
×

About Uncodemy

Know more about who we are and what we stand for.

About Us

About Us

Discover our mission to equip individuals with essential IT skills and industry expertise.

Read More →
UnCodeMy Gallery

UnCodeMy Gallery

Explore highlights from our IT training sessions and success stories.

Read More →
Mentors

Mentors

Meet the industry experts who train and guide you through your course.

Read More →
Free Resources

Free Resources

Free tutorials, interview questions, tools and calculators to help you learn.

Explore →
×

Free Resources

Learn, practice and plan your career — bilkul free.

Tutorials

Tutorials

Step-by-step written tutorials to learn any technology at your own pace.

Read More →
Free Counselling

Free Counselling

Book a free video counselling session and pick the right course for you.

Read More →
Online Compiler

Online Compiler

Write, run and test your code online — no setup required.

Read More →
MySQL Tutorial · Data Analytics

Exploratory Data Analysis (EDA) with SQL in MySQL

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_totalfirst_daylast_daycustomers
102026-08-012026-08-067

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_citynull_amountnull_status
110

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;
cityn
Delhi4
Pune2
Mumbai2
NULL1
Pune1

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_cleann
delhi4
pune3
mumbai2
NULL1

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_amtmax_amtavg_amtsd
640.0098000.0011978.8930416.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_idorder_dateamountcopies
12026-08-031200.002

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;
statusnpct
delivered880.0
returned110.0
cancelled110.0

Now you know revenue numbers should probably exclude cancelled and returned orders. Decide that rule in writing before you build a report.

Pitfalls

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.

Become a Job-Ready Data Analyst

Master MySQL, Advanced Excel, Power BI, Tableau and Python with Uncodemy's Data Analytics Training in Noida — live projects, expert mentors and Dedicated Career & Placement Assistance.

Enroll in Data Analytics Course →