CODEWITHSUNDEEP
sqloracle11g
user466534
user466534

Reputation:

select employees from table in oracle

hello guys i have following question related to following problem

From tables emp and dept find number of employees working in every departments by their jobs.
Showing number not more than 5.

i wrote following thing

select count(*)
from emp  e,dept d
where  e.department_id=d.department_id  and  count(*)<5

group by   e.department_id,e.job;

but it shows me following errors

Error at Command Line:3 Column:23
Error report:
SQL Error: ORA-00933: SQL command not properly ended
00933. 00000 -  "SQL command not properly ended"
*Cause:    
*Action:

what is problem?please help me

Upvotes: 1

Views: 323

Answers (3)

Taryn
Taryn

Reputation: 247870

Since you are trying to filter based on the count(*) value, you need to use a HAVING clause. A HAVING clause uses an aggregate function for filtering.

While there is nothing wrong with @JW's answer, I would change it slightly to use ANSI JOIN syntax instead of the JOIN syntax in the WHERE clause:

select count(*) Total
from emp e
inner join dept d
    on e.department_id=d.department_id  
group by e.department_id, e.job 
having count(*) < 5

Or you can make your query a subquery to filter using the WHERE:

select Total
from
(
   select count(*) Total
   from emp e
   inner join dept d
      on e.department_id=d.department_id  
   group by e.department_id, e.job 
) src
where total < 5

Upvotes: 3

Sashi Kant
Sashi Kant

Reputation: 13465

Try this:: having

select count(*)
from emp  e,dept d
where  e.department_id=d.department_id  

group by   e.department_id,e.job having count(*)<5;

Upvotes: 2

John Woo
John Woo

Reputation: 263913

try using HAVING clause

SELECT  COUNT(*)
FROM    emp  e 
        INNER JOIN dept d 
            ON e.department_id=d.department_id  
GROUP   BY   e.department_id,e.job
HAVING  count(*)<5 ;

Upvotes: 4

Related Questions