Find average salary of employees for each department and order employees within a department by age-CodePudding

CREATE TABLE EMPLOYEE (
    NAME VARCHAR(500) UNIQUE,
    AGE INT,
    DEPT VARCHAR(500),
    SALARY INT
)

INSERT INTO EMPLOYEE  VALUES('RAMESH',20,'FINANCE',50000);
INSERT INTO EMPLOYEE  VALUES('DEEP',25,'SALES',30000);
INSERT INTO EMPLOYEE  VALUES('SURESH',22,'FINANCE',50000);
INSERT INTO EMPLOYEE  VALUES('RAM',28,'FINANCE',20000);
INSERT INTO EMPLOYEE  VALUES('PRADEEP',22,'SALES',20000);

Could someone explain the error in the query

SELECT NAME, AGE, DEPT, AVG(SALARY)
FROM EMPLOYEE
GROUP BY DEPT 
ORDER BY AGE

(/* USING NAME,AGE ETC ALSO SHOWS ERROR- "column "employee.name" must appear in the GROUP BY clause or be used in an aggregate function")

Why is there an error; consider both the logic part and the syntax part for explanation?

CodePudding user response：

You can refer to the error on at here : must appear in the GROUP BY clause or be used in an aggregate function POSTGRESQL

SELECT DISTINCT NAME, AGE, DEPT
    , AVG(SALARY)
FROM EMPLOYEES
GROUP BY NAME, DEPT, AGE
ORDER BY AGE

CodePudding user response：

When you are using group by then you should extract only those columns which you used in group by and you can choose aggregate function with this. So you can use like below query.

select DEPT, AVG(SALARY) FROM EMPLOYEE GROUP BY DEPT