Home » SQL & PL/SQL » SQL & PL/SQL » Group by clause doubt - Can you please suggest me the query to this problem??? (Oracle, windows)
|
|
|
|
|
|
|
|
|
Re: Group by clause doubt - Can you please suggest me the query to this problem??? [message #632175 is a reply to message #632174] |
Mon, 26 January 2015 18:33 |
ravikanth_b
Messages: 42 Registered: November 2007 Location: Bay Area, CA
|
Member |
|
|
here is my test case:
drop table DEPT;
CREATE TABLE DEPT(dept_id number, deptname varchar2(50), dept_head_id number);
insert into dept values(1, 'IT',1);
insert into dept values(2, 'Services',6);
insert into dept values(3, 'HR',8);
select * from DEPT;
drop table EMP;
create table EMP(empid number, name varchar2(50), age number, sal number, dept_id number);
insert into EMP values (1,'BLAKE',23,2000,1);
insert into EMP values (2,'ROB',35,4000,2);
insert into EMP values (3,'KING',43,5000,3);
insert into EMP values (4,'SMITH',43,2400,2);
insert into EMP values (5,'ALLEN',54,3200,3);
insert into EMP values (6,'ADAMS',28,1400,2);
insert into EMP values (7,'FORD',38,1800,1);
insert into EMP values (8,'MILLER',46,1500,3);
insert into EMP values (9,'SCOTT',32,1350,1);
insert into EMP values (10,'CLARK',33,1350,3);
select * from emp;
select * from (
select e.* ,
count(*) over (partition by dept_id) cnt
from emp e
) emp
where emp.empid in (select dept_head_id from dept);
|
|
|
|
|
Goto Forum:
Current Time: Mon Mar 18 21:44:31 CDT 2024
|