Cyber Security · मराठी आवृत्ती

Aggregates, GROUP BY आणि HAVING

रवींद्र बागले यांच्या course वर आधारित · सहज मराठीत explanation

अनेक rows वरून count, total किंवा average काढणारी functions म्हणजे aggregate functions. GROUP BY सारख्या values चे groups करतो; HAVING त्या groups वर condition लावतो.

SELECT COUNT(*) AS total_students FROM students;
SELECT COUNT(email) FROM students;                     -- ignores NULLs → 9
SELECT SUM(fees_paid), AVG(fees_paid), MIN(age), MAX(age) FROM students;

SELECT city, COUNT(*) AS students FROM students GROUP BY city;
SELECT city, SUM(fees_paid) AS total_fees FROM students
  GROUP BY city ORDER BY total_fees DESC;

SELECT city, COUNT(*) AS students FROM students
  GROUP BY city HAVING COUNT(*) >= 2;                  -- only cities with 2+ students

SELECT city, AVG(age) AS avg_age FROM students
  WHERE fees_paid > 0                                  -- filter rows first
  GROUP BY city
  HAVING AVG(age) > 23;                                -- then filter groups

COUNT(*) rows मोजतो; COUNT(email) फक्त non-NULL emails. मूळ dataset मध्ये अनुक्रमे 10 आणि 9. SUM/AVG/MIN/MAX सामान्यतः NULL values वगळतात. त्यामुळे NULL age हा zero धरला जात नाही.

WHERE group बनण्याआधी rows filter करतो. HAVING aggregate/group result filter करतो. Source example मध्ये आधी fees_paid >0 rows, मग city groups आणि शेवटी AVG(age)>23 groups.

समजण्यासाठी logical क्रम: FROM → WHERE → GROUP BY → HAVING → SELECT → ORDER BY → LIMIT. हा database engine चा अनिवार्य physical execution plan नाही; optimizer वेगळी योजना करू शकतो.

Practice

प्रत्येक city साठी student count आणि total fees; total fees 20000 पेक्षा जास्त असलेल्या cities; enrollments मधून प्रत्येक course चा average score काढा.