How to deal with: Error Code 1054. Unknown column 'dept' in 'field list'

Question

I went through already: Error Code: 1248. Every derived table must have its own alias No solution found for query, but still not sure what correct column name its expecting ?

Case-1:

SELECT max(avg_sal), min(avg_sal) 
FROM (SELECT dept , avg(salary) as avg_sal from Employee e  GROUP BY dept HAVING dept in ('Admin','IT'));

12:04:44 SELECT max(avg_sal), min(avg_sal) FROM (SELECT dept , avg(salary) as avg_sal from Employee e GROUP BY dept HAVING dept in ('Admin','IT')) LIMIT 0, 1000 Error Code: 1248. Every derived table must have its own alias 0.000 sec

Case:2

SELECT max(avg_sal), min(avg_sal) 
FROM (SELECT dept , avg(salary) as avg_sal from Employee e  GROUP BY dept HAVING dept in ('Admin','IT')) as dept;

then I get

SELECT max(avg_sal), min(avg_sal) FROM (SELECT dept , avg(salary) as avg_sal from Employee e GROUP BY dept HAVING dept in ('Admin','IT')) as dept;

Dept

@Entity
public class Dept {
    @Id
    @GeneratedValue
    private Integer id;
    private String name;
}

Employee

@Entity
@NamedQuery(name = "Employee.findMaxSalariesByDept", 
            query = "SELECT e.dept, MAX(e.salary) FROM Employee e GROUP BY e.dept.name HAVING e.dept.name in ?1")
public class Employee {
    @Id
    @GeneratedValue
    private Long id;
    private String name;
    @ManyToOne(cascade = CascadeType.ALL)
    private Dept dept;
    private int salary;
}

Show Table:

CREATE TABLE `employee` (
   `id` bigint(20) NOT NULL AUTO_INCREMENT,
   `name` varchar(255) DEFAULT NULL,
   `salary` int(11) NOT NULL,
   `dept_id` int(11) DEFAULT NULL,
   PRIMARY KEY (`id`),
   KEY `FKfow2bhgypdy2ij4oyukrn6cqw` (`dept_id`)
 ) ENGINE=MyISAM AUTO_INCREMENT=6 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;

Could you add the output of `SHOW CREATE TABLE Employee;` to your question? It seems that this table lacks the column `dept` — digijay, Aug 16 '19 at 07:04
As @scaisEdge mentions below you don't have a `dept` column but only `dept_id`. Could you try to change that in your Case-1 query? — digijay, Aug 16 '19 at 08:42

ScaisEdge · Answer 1 · 2019-08-16T07:58:50.747

0

first case you need a table alias for the FROM() clause eg: FROM ( ) T

SELECT max(T.avg_sal)
    , min(T.avg_sal) 
FROM ( 
    SELECT dept_id dept, avg(salary) as avg_sal 
    from Employee e  
    GROUP BY dept_id
    WHERE dept_id  in ('Admin','IT')
) T  ;

and you should use where not having for filter

Second case (don't use for table alias the same name you use for column eg:dept but use t_dept)

SELECT max(t_dept.avg_sal), min(t_dept.avg_sal) 
FROM ( 
  SELECT dept_id dept, avg(salary ) as avg_sal 
   from Employee e  
   WHERE  dept_id in ('Admin','IT')
  GROUP BY dept
) as t_dept;

and you could use where for filter do the fact that these values are not calculated but are available in the rows content

edited Aug 16 '19 at 07:58

answered Aug 16 '19 at 07:01

ScaisEdge

131,976
10
91
107

I am still getting the same error: 13:25:21 `SELECT max(t_dept.avg_sal), min(t_dept.avg_sal) FROM ( SELECT dept , avg(salary ) as avg_sal from Employee e WHERE dept in ('Admin','IT') GROUP BY dept ) as t_dept LIMIT 0`, 1000 Error Code: 1054. Unknown column 'dept' in 'field list' 0.000 sec – PAA Aug 16 '19 at 07:55
1

you have not a dept column in your table (you have dept_id) .. answer updated with proper column name (and column alias) – ScaisEdge Aug 16 '19 at 08:00

How to deal with: Error Code 1054. Unknown column 'dept' in 'field list'

1 Answers1