The 365 Data Science team is proud to invite you to our own community forum. A very well built system to support your queries, questions and give the chance to show your knowledge and help others in their path of becoming Data Science specialists.
Ask
Anybody can ask a question
Answer
Anybody can answer
Vote
The best answers are voted up and moderated by our team

Average function in SQL

Average function in SQL

0
Votes
1
Answer

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. 

#
1 Answer

365 Team
0
Votes

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
 
 

Oh right I get it now. It’s a record of every single salary figure, so when there’s a salary change, another line is added with another date for that employee. Now everything makes sense. Thank you!

6 months

Great! You are welcome!

6 months
×
Learn Data Science
this Summer!
Get 50% OFF