Average function in SQL

Question

Hi, I am confused about the AVG function in the HAVING lectures. I was working through the exercise of trying to filter out salaries and average salaries that are over $120,000. What is AVG of the salaries actually calculating? What is it averaging precisely? I don't get how average of the salary is different for every employee number.

Answer 1

Hi Josh! Thanks for reaching out! What the AVG() function will calculate for us depends on the structure of the query and the specific fields we've indicated in it. In the example you are referring to, we GROUP BY employee number. This indeed aggregates all salary values that have been provided for a certain employee number (i.e. for each employee). Then, having obtained this aggregate value (although it is not displayed, the computer does calculate it), we request to retrieve the average values of only those employees, whose average salary is greater than $120,000. We do that by adding the following clause:

HAVING AVG(salary) > 120000

Also, it is the HAVING clause that allows us to apply the subsequent aggregate function (in this case - AVG()) to the values as obtained by the GROUP BY statement. If you need further clarification, you can execute the following query to see what data there is in the salaries table. As you can see, there are multiple records for each contract that has been signed by every employee.

SELECT 
    *
FROM
    salaries;

Hope this helps.
Best,
Martin

Average function in SQL

Submit an answer