Super learner
This user is a Super Learner. To become a Super Learner, you need to reach Level 8.
Last answered:

25 Jul 2022

Posted on:

23 Jul 2022

0

What do you mean by average salary?

Good morning. I have this doubt: when I run the code
SELECT
    emp_no, AVG(salary)
FROM
    salaries
GROUP BY emp_no
HAVING AVG(salary) > 120000;

the first row is emp_no = 11486, so I think that AVG(salary) calculates the average of several salaries belonging to emp_no 11486 along the years. But when I run the code
SELECT
    *, AVG(salary)
FROM
    salaries
where emp_no=11486;

without the "group by" function, I expect to see several rows from the same emp_no 11486, but there is just 1. To see all the rows from emp_no 11486 I have noticed I have not to show AVG(salary) in the SELECT statement:
SELECT
    *
FROM
    salaries
where emp_no=11486;

Why? With the 2nd code I expect to see the same result of listing all the salaries of emp_no 11486, where each average is equal to the average of all salaries of all employees (or maybe to the average of all salaries of employee number 11486?), so always the same number. But the result is different. Thank you

1 answers ( 0 marked as helpful)
Instructor
Posted on:

25 Jul 2022

0

Hi Alessandro!
Thanks for reaching out.

The reason is because you use an aggregate function and in the WHERE clause you specify a particular employee. So, it calculates all of his salaries and give you the average. When you do not use an aggregate function in the 3rd example you obtain all of his 18 salaries.

Hope this helps.
Best,
Tsvetelin

Submit an answer