Aggregation & Reporting

Aggregate functions perform calculations across multiple rows and return a single value. COUNT(), SUM(), AVG(), MIN(), and MAX() are the building blocks of business reports. Combined with GROUP BY, you can summarize data by category — like counting students per program or calculating average age per city.

• COUNT(*) counts all rows in a group.
• AVG(age) calculates the average age.
• GROUP BY program groups results by each unique program name.
• Use HAVING (not WHERE) to filter after aggregation.

💡 Practical Skill: Build reports that NGOs and businesses need — e.g., "Number of beneficiaries per region" or "Total monthly expenses." Use MySQL Workbench to export query results to Excel for further analysis.

-- Count total studentsSELECT COUNT(*) AS total_students FROM students; -- Average age of all studentsSELECT AVG(age) AS average_age FROM students; -- Count students per programSELECT program, COUNT(*) AS student_countFROM studentsGROUP BY program; -- Average age per city (only cities with at least 1 student)SELECT city, AVG(age) AS avg_age, COUNT(*) AS countFROM studentsGROUP BY cityHAVING COUNT(*) >= 1ORDER BY avg_age DESC; -- Find the oldest student overallSELECT name, age FROM studentsWHERE age = (SELECT MAX(age) FROM students);