Misuse of aggregate function AVG() in SQL-CodePudding

I have an Employees table which looks like this:

employee_id employee_name employee_salary 
1              Tom             35000          
2              Sarah           50000          
3              David           45000         
4              Rosie           55000         
5              Michael         45000

I need to return the employees salary that is higher than the average salary but the below command is having an error saying '1 misuse of aggregate function AVG()'.

SELECT employee_salary
FROM Employees
WHERE employee_salary > AVG(employee_salary);

The output that I'm expecting to get is:

employee_id employee_name employee_salary          
2              Sarah           50000          
4              Rosie           55000

Please advise, thank you!

CodePudding user response：

I need to write the SQL query to return the number of employees for each department.

I assume you're looking for something like this:

SELECT department_id
   ,COUNT(employee_id) AS TotalEmployees
FROM Department
LEFT JOIN Employees
  ON Employees.department_id = Department.department_id
GROUP BY department_id

Also, I need to return the employees salary that is higher than the average salary

The simplest way to return the salaries that are higher than average as a beginner sql programmer is probably something like this:

SELECT employee_salary
FROM Employees
WHERE employee_salary > (SELECT AVG(employee_salary)
                         FROM Employees)

As the others said, the other questions just require a bit of research. There are tonnes of resources out there to learn, but it takes time...

CodePudding user response：

I need to write the SQL query to return the number of employees for each department. However, my below command is not correct:

This is not what you ask for.

You get the join correct, but you ask for:

SELECT COUNT(Employees.employment_id)

The count how often different employment id's exist - which is 1 for an employee in one department, or X with X being the number of entries in the join. As the department_id entry is part of the employee table, this CAN NOT HAPPEN. TOTALLY not asking what you want.

I'm using the LEFT JOIN here because I am returning the result from the Employees table is this right?

Depends - a normal join should work here. Left is only sensible if the other side can be empty - which I would assume is not possible (there are no rows with Employees.department_id being NULL).

You you want is a count (without anything in the brackets) and a group by department_id. And obviously the department id:

SELECT Department.department_id, count() FROM....

Furthermore, are there any tips to speed up SQL Server's performance?

Just pointing you to https://use-the-index-luke.com/ - indices are a cornerstone for any decent performance.

Ignoring your second question - one per question please.