We use MAX and COUNT to summarize data. Each function service a unique purpose:

• AVG(): Calculate the average value from a specified field
• SUM(): adds up all the values in a specific field
• MAX() and MIN(): find the highest and lowest values in a specific field, respectively
• COUNT(): counts the number of non-missing (not-null) records in a file

Application-> This functions can be applied to both numerical and non-numerical fields, with AVG() and SUM() being exclusive to numerical data due to their arithmetic nature. In contrast, COUNT(), MIN(), and MAX() can be used with non-numerical fields as well.

How to work with ROUND:

SELECT ROUND(AVG(facebook_likes),1)as avg_facebook_likes
FROM reviews;

Filter HAVING

It works similarly to WHERE in that, it is a filtering clause, with the difference that HAVING filters grouped data.

Filtering grouped data can be convenient when working with a large dataset. When working with thousands or even millions of rows, HAVING will allow you to filter for just the group of data you want, such as films over two hours long!

Example:

-- Select the country and distinct count of certification as certification_count
SELECT country, COUNT(DISTINCT certification) as certification_count
FROM films
-- Group by country
GROUP BY country
-- Filter results to countries with more than 10 different certifications
HAVING COUNT(DISTINCT certification)>10

Example:

• Select the release_year for each film in the films table, filter for records released after 1990, and group by release_year.
• Modify the query to include the average budget aliased as avg_budget and average gross aliased as avg_gross for the results we have so far.
• Modify the query once more so that only years with an average budget of greater than 60 million are included.
• Finally, order the results from the highest average gross and limit to one.

SELECT release_year, AVG(budget) AS avg_budget, AVG(gross) AS avg_gross
FROM films
WHERE release_year > 1990
GROUP BY release_year
HAVING AVG(budget) > 60000000
ORDER BY AVG(gross) DESC
LIMIT 1;