Click here to Skip to main content
15,901,205 members
Please Sign up or sign in to vote.
0.00/5 (No votes)
See more:
I have an SQL database and I need to make a procedure that takes the department id from the user and updates the salary of the employees where the salary is less then average .

ERROR:
An aggregate may not appear in the WHERE clause unless it is in a subquery contained in a HAVING clause or a select list, and the column being aggregated is an outer reference.


What I have tried:

CREATE PROCEDURE RAISE_SALARY(
@P_DEPARTMENT_ID NUMERIC
) 
AS
BEGIN
UPDATE EMP_DETAILS 
SET SALARY = SALARY + SALARY * 0.25 
WHERE SALARY < AVG(SALARY)
AND DEPARTMENT_ID = @P_DEPARTMENT_ID;
END
Posted
Updated 23-Jan-23 9:24am
v2
Comments
0x01AA 23-Jan-23 12:24pm    
a.) What is the error message if there is one?
b.) You can't get the average that simple by AVG(SALARY). Most easy you select avarage in advance.
Something like SELECT AVG(SALARY) FROM EMP_DETAILS WHERE DEPARTMENT_ID = @P_DEPARTMENT_ID INTO @AVG_SALARY

After that use that @AVG_SALARY as parameter for the update SQL.
UPDATE EMP_DETAILS
SET SALARY = SALARY + SALARY * 0.25
WHERE SALARY < @AVG_SALARY
AND DEPARTMENT_ID = @P_DEPARTMENT_ID;
jenkins123 23-Jan-23 13:04pm    
something like this (sorry to bother but I am not experienced with procedures)

CREATE PROCEDURE RAISE_SALARY(
@P_DEPARTMENT_ID NUMERIC
)
AS
DECLARE @AVG_SALARY INT
BEGIN
SELECT AVG(SALARY) FROM EMP_DETAILS WHERE DEPARTMENT_ID = @P_DEPARTMENT_ID INTO @AVG_SALARY ;
UPDATE TST_EMP
SET SALARY = SALARY + SALARY * 0.25
WHERE SALARY < @AVG_SALARY
END
GO
0x01AA 23-Jan-23 13:09pm    
Yes, but don't foget to include also DEPARTMENT_ID in the UPDATE WHERE clause. I updated my first comment, see there.

And it is very ok to ask back, no need to be sorry ;)
0x01AA 23-Jan-23 15:13pm    
Sorry from my side for the confusion from my side. I mixed up the syntax of mssql with another server.
For mssql it needs to be more something like:

...
DECLARE @AVG_SALARY INT

SELECT @AVG_SALARY = (SELECT AVG(SALARY) FROM EMP_DETAILS WHERE DEPARTMENT_ID = @P_DEPARTMENT_ID)

UPDATE EMP_DETAILS
SET SALARY = SALARY + SALARY * 0.25
WHERE SALARY < @AVG_SALARY
AND DEPARTMENT_ID = @P_DEPARTMENT_ID

1 solution

Please see also all the comments to the question...
... and please take care I'm not very familar with mssql syntax, therefore +- some semicolons can be wrong.

Anyway this should do what you need:
CREATE PROCEDURE RAISE_SALARY(@P_DEPARTMENT_ID NUMERIC) 
AS
BEGIN
	DECLARE @AVG_SALARY INT
    /* Determine the average salary for the specific department  '@P_DEPARTMENT_ID' */
	SELECT @AVG_SALARY = AVG(SALARY) 
				FROM EMP_DETAILS 
				WHERE DEPARTMENT_ID = @P_DEPARTMENT_ID

	/* Update the salaries for that department for salaries below the average */
	UPDATE EMP_DETAILS 
		SET SALARY = SALARY + SALARY * 0.25 
	WHERE SALARY <  @AVG_SALARY
	  AND DEPARTMENT_ID = @P_DEPARTMENT_ID
END


I hope it helps.
 
Share this answer
 
v5
Comments
0x01AA 23-Jan-23 15:50pm    
Finally after five updates, I hope it fits :(

[Edit]
@AVG_SALARY as INTEGER does not really make sense, but let us take that for finetuning...
jenkins123 24-Jan-23 2:58am    
Thanks for the help <3

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)



CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900