There are some useful aggregate functions in MySQL that you can use with a group of values.
COUNT() – counts the number of rows
MIN() – gets the minimum value
MAX() – gets the maximum value
SUM() – sums up the values
AVG() – calculates the average of the values
SELECT COUNT(*)
FROM Movie
WHERE Rating = ’R’;
SELECT MAX(FinalExamGrade)
FROM Student;
SELECT AVG(FinalExamGrade)
FROM Student;
The GROUP BY clause is usually used along with aggregate functions, which groups rows with matching values into a set of summary rows, with one row for each group.
SELECT ReleaseYear, COUNT(*)
FROM Movie
GROUP BY ReleaseYear;
ORDER BY ReleaseYear;
You can order the results using ORDER BY also. Place the GROUP BY clause before the ORDER BY clause and after the optional WHERE clause.
To filter the group results, use the HAVING clause. Place the HAVING clause after the GROUP BY clause but before the optional ORDER BY clause.
SELECT ReleaseYear, Title, COUNT(*)
FROM Movie
GROUP BY ReleaseYear, Title
HAVING COUNT(*) GREATER_THAN_OR_EQUAL_TO 2;
SELECT Name, Team, MAX(Points)
FROM BasketballPlayer
GROUP BY Name, Team,
HAVING MAX(Points) GREATER_THAN 30
ORDER BY Name, MAX(Points);
There are additional functions that you can use in your SQL query.
Numeric functions:
String functions
Date and time functions:
Subscribe to Appficial for more programming videos coming soon. Also, don't forget to click LIKE and comment on the video if it helped you out!
