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

Question

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?

MySQL <> SQL Server <> PostgreSQL - please tag your *single* RDBMS. — Dale K, Sep 07 '22 at 03:13
The error is self explanatory... when grouping, the selected columns must either be grouped by or aggregated. — Dale K, Sep 07 '22 at 03:14
Put it this way, if you are `GROUP BY DEPT`, there will only one row per `DEPT`, so which name/age do you expect there to be in your output? — danblack, Sep 07 '22 at 03:17
I removed the conflicting RDBMS tags... feel free to add the correct one back. — Dale K, Sep 07 '22 at 03:43

score 0 · Answer 1 · edited Sep 07 '22 at 03:25

0

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

edited Sep 07 '22 at 03:25

Dale K

25,246
15
42
71

answered Sep 07 '22 at 03:18

lavantho0508

125
10

You don't need `DISTINCT` if you are using `GROUP BY`. – Dale K Sep 07 '22 at 03:25
1

But it will work perfectly without – Dale K Sep 07 '22 at 03:29

score 0 · Answer 2 · answered Sep 07 '22 at 03:25

0

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

answered Sep 07 '22 at 03:25

Anand Tiwari

1
2

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

2 Answers2