Lesson 08 � Data Analysis

DATA KO
SUMMARIZE KARO.

School mein average marks, shop mein total sales, website pe daily visitors � sab aggregate functions se nikalte hain. GROUP BY se category-wise analysis hoti hai. Yeh data analysis ki foundation hai.

WHY: aggregate functions kyun?

Raw data mein se insights nikalna � yeh data analyst ka kaam hai. Aggregate functions single row nahi, poore dataset ko summarize karte hain. "Kitne students hain?" � COUNT se pata chalega. "Average kitna hai?" � AVG batayega. "Sabse zyada marks kisne liye?" � MAX se dikhega.

HOW: COUNT kaise use karein

Count sabse basic aggregate function hai. Kitni rows hain, kitne unique students hain, kitne courses available hain � sab COUNT se hota hai.

-- Kitne rows hain students table mein
SELECT COUNT(*) FROM students;

-- Kitne unique courses hain
SELECT COUNT(DISTINCT course) FROM students;

-- Kitne students ko marks mila hai (NULL nahi)
SELECT COUNT(marks) FROM students;

-- Alias ke saath
SELECT COUNT(*) AS total_students FROM students;

SUM & AVG: total aur average

Numeric columns ka total aur average nikalna � yeh business analysis mein sabse zyada use hota hai. "Total kitna sales hua?" � SUM. "Average marks kitne hain?" � AVG.

-- Total marks sabka
SELECT SUM(marks) FROM students;

-- Average marks
SELECT AVG(marks) FROM students;

-- Course-wise total aur average
SELECT course, SUM(marks) AS total, AVG(marks) AS average
FROM students
GROUP BY course;

-- ROUND se average ko 2 decimal tak
SELECT course, ROUND(AVG(marks), 2) AS avg_marks
FROM students
GROUP BY course;

MIN & MAX: chhota aur bada value

Kisi bhi column mein sabse kam aur sabse zyada value dhundna. "Sabse kam marks kitne aaye?" � MIN. "Sabse zyada salary kiski hai?" � MAX.

-- Minimum marks
SELECT MIN(marks) FROM students;

-- Maximum marks
SELECT MAX(marks) FROM students;

-- Dono ek saath
SELECT MIN(marks) AS lowest, MAX(marks) AS highest FROM students;

-- Course-wise min aur max
SELECT course, MIN(marks) AS min_marks, MAX(marks) AS max_marks
FROM students
GROUP BY course;

GROUP BY: category-wise grouping

GROUP BY sabse powerful feature hai data analysis ka. Isse data ko categories mein divide karke har category ka summary nikal sakte ho. "Har course mein kitne students hain?" � GROUP BY se hota hai.

-- Course-wise student count
SELECT course, COUNT(*) AS students
FROM students
GROUP BY course;

-- Course-wise average marks
SELECT course, AVG(marks) AS avg_marks
FROM students
GROUP BY course;

-- Multiple columns se grouping
SELECT course, age, COUNT(*) AS count
FROM students
GROUP BY course, age;

-- ORDER BY ke saath � highest average pehle
SELECT course, AVG(marks) AS avg_marks
FROM students
GROUP BY course
ORDER BY avg_marks DESC;

HAVING: filtered groups

WHERE rows filter karta hai, HAVING groups filter karta hai. "Sirf woh courses dikhao jinke average marks 80 se zyada hain" � yeh HAVING se hota hai.

-- Courses jinke average marks 80 se zyada hain
SELECT course, COUNT(*) AS students, AVG(marks) AS avg_marks
FROM students
GROUP BY course
HAVING AVG(marks) > 80;

-- Courses jismein 5 se zyada students hain
SELECT course, COUNT(*) AS students
FROM students
GROUP BY course
HAVING COUNT(*) > 5;

-- WHERE aur HAVING dono
SELECT course, AVG(marks) AS avg_marks
FROM students
WHERE age > 18
GROUP BY course
HAVING AVG(marks) > 75;

Try it: SQL query likho

Neeche ka editor SQL jaisa hai. Yahan apni query likho aur "Run Query" dabao. Screen par sample results dikhenge � yeh browser-based simulation hai, real database nahi.

Aggregate functions ki summary

Har aggregate function ka apna kaam hai. Ek table mein saare functions ek saath use kar sakte ho � comprehensive analysis hoti hai.

-- Comprehensive summary
SELECT
 COUNT(*) AS total_students,
 COUNT(DISTINCT course) AS total_courses,
 SUM(marks) AS total_marks,
 AVG(marks) AS average_marks,
 MIN(marks) AS lowest_marks,
 MAX(marks) AS highest_marks
FROM students;

-- Course-wise comprehensive
SELECT
 course,
 COUNT(*) AS students,
 SUM(marks) AS total_marks,
 ROUND(AVG(marks), 2) AS avg_marks,
 MIN(marks) AS min_marks,
 MAX(marks) AS max_marks
FROM students
GROUP BY course
ORDER BY avg_marks DESC;

Common beginner mistakes

• GROUP BY bhoolna: Agar SELECT mein non-aggregate column hai aur aggregate bhi hai, toh GROUP BY zaroori hai. Warna error aayega!
• WHERE vs HAVING: WHERE rows filter karta hai (GROUP BY se pehle), HAVING groups filter karta hai (GROUP BY ke baad). Dono ek jagah mat lagao.
• NULL samajhna: COUNT(*) NULL rows bhi count karta hai, lekin COUNT(column) NULL skip karta hai. SUM aur AVG bhi NULL skip karte hain.
• Alias ka galat use: HAVING clause mein alias use nahi kar sakte � HAVING avg_marks > 80 kaam nahi karega, HAVING AVG(marks) > 80 likho.