Missing expression problem in SQL using Oracle

Question

I want to get the number of employees by department and I wrote this script using Oracle but it always says that there is a missing expression

The columns used in my tables :

department :name (the name of the department) - 
depnum (the id of the department"primary key"), 
employee : empnum (the id of the employee) - 
depnum (the id of the department in which the employee in question is working "foreign key")

Query:

select 
    s.name 
from
    department s 
inner join 
    employee p on s.depnum = p.depnum 
group by 
    s.name 
having 
    count(p.empnum) = max(select count(p.empnum) 
                          from employee p, department s 
                          where s.depnum = p.depnum 
                          group by s.name) ;

Your question is not clear. try to put some screenshots of required output and some sample input data. — Waqas Shabbir
– Waqas Shabbir, Commented Jan 5, 2019 at 16:33
Bad habits to kick : using old-style JOINs - that old-style comma-separated list of tables style was replaced with the proper ANSI JOIN syntax in the ANSI-92 SQL Standard (more than 25 years ago) and its use is discouraged — marc_s
– marc_s, Commented Jan 5, 2019 at 16:55
okay thank you , from this point onward i will be wary of it thank you — Ab Hamid Sajid
– Ab Hamid Sajid, Commented Jan 5, 2019 at 17:10

Gordon Linoff · Accepted Answer · 2019-01-05 16:34:20Z

1

If you want the number of employees by department, I would expect something like this:

select s.name, count(*) as num_employees
from department s inner join
     employe p
     on s.depnum = p.depnum 
group by s.name ;

If you want the department names with the maximum number of names, you can use a having clause:

select s.name, count(*) as num_employees
from department s inner join
     employe p
     on s.depnum = p.depnum 
group by s.name 
having count(*) = (select max(cnt)
                   from (select count(*) as cnt
                         from employee e2
                         group by e2.depnum
                        ) e2
                  );

The problem with your query is that you are attempting to take the max() of a subquery. That syntax is not allowed -- and not necessary.

answered Jan 5, 2019 at 16:34

Gordon Linoff

1.3m62 gold badges706 silver badges857 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

Gordon Linoff Over a year ago

@AbHamidSajid . . . The very first sentence in your question is: "I want to get the number of employees by department ".

Ab Hamid Sajid Over a year ago

your answer helped me a lot thank you so much i just need to take off the count(*) to get the exact result i need

Ab Hamid Sajid Over a year ago

yeah i'm truly sorry i should've motioned that in my question

Zaynul Abadin Tuhin · Accepted Answer · 2019-01-05 16:32:49Z

0

you sql statement is not correct that's why it thrown that error. I think you tried something like below

select s.name 
from department s 
inner join employe p on s.depnum=p.depnum 
group by s.name 
having count(p.empnum)=
select max(cnt) from 
( 
select count(p.empnum) as cnt
from employe p join department s 
on s.depnum=p.depnum 
group by s.name
 ) t;

answered Jan 5, 2019 at 16:32

Zaynul Abadin Tuhin

32.1k6 gold badges37 silver badges66 bronze badges

Collectives™ on Stack Overflow

Missing expression problem in SQL using Oracle

2 Answers 2

3 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

3 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related