# Aggregates, GROUP BY आणि HAVING

Source: https://ravindrabagale.com/mr/cyber/part-05/ch10-mysql-part-1-install-create-insert-and-select/10-8-aggregate-functions-group-by-and-having.html
Language: mr (Marathi with English technical terms)

अनेक 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 धरला जात नाही.

 | Clause
 | Filters
 | Can use aggregates?

 | WHERE
 | Individual rows, before grouping
 | No

 | HAVING
 | Groups, after grouping
 | Yes

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 काढा.

रवींद्र बागले यांची tip

WHERE COUNT(*) > 1 लिहू नका. Aggregate वर filter साठी HAVING वापरा. आधी कोणते rows घ्यायचे आणि मग कोणते groups ठेवायचे हे वेगळं ठरवा.