Aggregate Functions & GROUP BY

-- COUNT — किती rows?
SELECT COUNT(*) AS total_students FROM students;
SELECT COUNT(email) AS students_with_email FROM students;  -- NULL skip
SELECT COUNT(DISTINCT city) AS unique_cities FROM students;

-- SUM — total
SELECT SUM(marks) AS total_marks FROM students;
SELECT SUM(marks) AS pune_total FROM students WHERE city = 'Pune';

-- AVG — average
SELECT AVG(marks) AS class_average FROM students;
SELECT ROUND(AVG(marks), 2) AS avg_marks FROM students;

-- MIN / MAX
SELECT MIN(marks) AS lowest,  MAX(marks) AS highest FROM students;
SELECT MIN(name) AS first_alpha FROM students;  -- alphabetically

-- एकाच query मध्ये सगळे
SELECT
    COUNT(*)     AS total,
    ROUND(AVG(marks), 1) AS average,
    MIN(marks)   AS lowest,
    MAX(marks)   AS highest,
    SUM(marks)   AS total_marks
FROM students;

-- प्रत्येक city मध्ये किती students?
SELECT city, COUNT(*) AS student_count
FROM students
GROUP BY city;
-- Pune: 3, Mumbai: 2, Nagpur: 1 ...

-- प्रत्येक city चे average marks
SELECT city, ROUND(AVG(marks), 1) AS avg_marks
FROM students
GROUP BY city
ORDER BY avg_marks DESC;

-- City नुसार detailed stats
SELECT
    city,
    COUNT(*)           AS students,
    ROUND(AVG(marks), 1) AS avg_marks,
    MAX(marks)           AS top_score,
    SUM(marks)           AS total_marks
FROM students
GROUP BY city
ORDER BY avg_marks DESC;

-- HAVING — GROUP BY नंतर filter करतो
-- WHERE — GROUP BY आधी rows filter करतो

-- 2 पेक्षा जास्त students असलेल्या cities
SELECT city, COUNT(*) AS student_count
FROM students
GROUP BY city
HAVING COUNT(*) > 2;

-- Average 75 पेक्षा जास्त असलेल्या cities
SELECT city, ROUND(AVG(marks), 1) AS avg
FROM students
GROUP BY city
HAVING AVG(marks) > 75
ORDER BY avg DESC;

-- WHERE + GROUP BY + HAVING एकत्र
SELECT city, COUNT(*) AS passed_students
FROM students
WHERE marks >= 40          -- आधी failed filter
GROUP BY city
HAVING COUNT(*) >= 2       -- नंतर count filter
ORDER BY passed_students DESC;