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

Biswajit Patnaik 0
Viewed 55
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?

CAN ANYBODY PROVIDE THE ANSWER USING SUB QUERY?

sql
2 Answers
lavantho05080

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
Anand Tiwari0
Related