Scribd
Upload
3 views2 pages

MySQL Aggregate Functions

The document explains aggregate functions in MySQL, which perform calculations on a set of values and return a single result, including COUNT, SUM, AVG, MAX, and MIN. It details the usage of the GROUP BY clause to group rows with the same values and the HAVING clause to filter grouped records. Additionally, it highlights the differences between WHERE and HAVING clauses in terms of their application and functionality.

Uploaded by

Sharvari
Copyright
© All Rights Reserved
We take content rights seriously. If you suspect this is your content, claim it here.
Available Formats
Download as DOCX, PDF, TXT or read online on Scribd
3 views2 pages

MySQL Aggregate Functions

The document explains aggregate functions in MySQL, which perform calculations on a set of values and return a single result, including COUNT, SUM, AVG, MAX, and MIN. It details the usage of the GROUP BY clause to group rows with the same values and the HAVING clause to filter grouped records. Additionally, it highlights the differences between WHERE and HAVING clauses in terms of their application and functionality.

Uploaded by

Sharvari
Copyright
© All Rights Reserved
We take content rights seriously. If you suspect this is your content, claim it here.
Available Formats
Download as DOCX, PDF, TXT or read online on Scribd

Aggregate Functions in MySQL

Aggregate functions perform calculations on a set of values and return a single result.
Function Purpose Example
COUNT( Counts the number of rows SELECT COUNT(*) FROM Student;
)
SUM() Returns the total of a numeric SELECT SUM(fees) FROM Student;
column
AVG() Returns the average value SELECT AVG(marks) FROM Student;
MAX() Returns the highest value SELECT MAX(marks) FROM
Student;
MIN() Returns the lowest value SELECT MIN(fees) FROM Student;

GROUP BY Clause
The GROUP BY clause is used to group rows that have the same values in specified columns.
It is commonly used with aggregate functions.
Syntax:
SELECT column_name, aggregate_function(column_name) FROM table_name
GROUP BY column_name;
Examples
1. Display the number of students in each city.
SELECT city, COUNT(*) FROM Student GROUP BY city;
2. Display the average marks of students in each class.
SELECT class, AVG(marks) FROM Student GROUP BY class;
3. Display the total fees collected from each city.
SELECT city, SUM(fees) FROM Student
GROUP BY city;

HAVING Clause
The HAVING clause is used to filter grouped records after applying the GROUP BY clause.
Syntax:
SELECT column_name, aggregate_function(column_name)
FROM table_name
GROUP BY column_name
HAVING condition;
Examples
1. Display cities where the total fees collected are greater than ₹60,000.
SELECT city, SUM(fees) FROM Student GROUP BY city HAVING SUM(fees) >
60000;
2. Display classes having an average marks greater than 70.
SELECT class, AVG(marks) FROM Student GROUP BY class HAVING
AVG(marks) > 70;
3. Display cities having more than one student.
SELECT city, COUNT(*)
FROM Student
GROUP BY city
HAVING COUNT(*) > 1;

Difference Between WHERE and HAVING


WHERE HAVING
Filters rows before grouping Filters groups after grouping
Cannot use aggregate functions Can use aggregate functions
Used before GROUP BY Used after GROUP BY

You might also like