Home » SQL & PL/SQL » SQL & PL/SQL » Group by clause doubt - Can you please suggest me the query to this problem??? (Oracle, windows)
Group by clause doubt - Can you please suggest me the query to this problem??? [message #630257] Tue, 23 December 2014 19:13 Go to next message
PHCR
Messages: 3
Registered: December 2014
Junior Member
Hello All,

Can somebody please help to solve this problem???/forum/fa/12373/0/

Problem description:
I have two tables employee and department. Both tables with data are attached.
My requirement is to display to department head and head count in each department.
I tried to use group by, join with group by but couldn't get output myself.
Can somebody please help me from this group to solve this problem.

Thanks,
Hemachandra
  • Attachment: employee.PNG
    (Size: 17.82KB, Downloaded 2313 times)
Re: Group by clause doubt - Can you please suggest me the query to this problem??? [message #630259 is a reply to message #630257] Tue, 23 December 2014 19:22 Go to previous messageGo to next message
Solomon Yakobson
Messages: 3267
Registered: January 2010
Location: Connecticut, USA
Senior Member
This is simple join between employee and department based on dept_id and then group by with count and any aggregate function, e.g. max over CASE statement based on emp_id = dept_head_id. Post what you came up with.

SY.

[Updated on: Tue, 23 December 2014 19:23]

Report message to a moderator

Re: Group by clause doubt - Can you please suggest me the query to this problem??? [message #630261 is a reply to message #630259] Tue, 23 December 2014 20:17 Go to previous messageGo to next message
PHCR
Messages: 3
Registered: December 2014
Junior Member
Thanks for your quick response SY.

I tried with below queries.

1. select e.name, count(e.dept_id) from employee e, dept d where e.empid=d.dept_head_id group by e.dept_id
2. select e.name, count(e.dept_id) from employee e join dept d on e.empid=d.dept_head_id group by e.dept_id

Above error is showing up when I tried to execute these.

/forum/fa/12374/0/

Thanks,
Hemachandra
Re: Group by clause doubt - Can you please suggest me the query to this problem??? [message #630262 is a reply to message #630261] Tue, 23 December 2014 20:26 Go to previous messageGo to next message
Solomon Yakobson
Messages: 3267
Registered: January 2010
Location: Connecticut, USA
Senior Member
select  max(
            case d.dept_head_id
              when e.empid then e.name
            end
           ) department_head,
        count(*) department_employee_count
  from  employee e,
        dept d
  where e.empid = d.dept_id
  group by e.dept_id


SY.
Re: Group by clause doubt - Can you please suggest me the query to this problem??? [message #630266 is a reply to message #630262] Tue, 23 December 2014 20:52 Go to previous messageGo to next message
PHCR
Messages: 3
Registered: December 2014
Junior Member
Thanks for the query.

But When I execute this, I am getting output as below and required output is different as shown in below image.


/forum/fa/12375/0/

Thanks,
Hemachandra
  • Attachment: output.PNG
    (Size: 7.21KB, Downloaded 1561 times)
Re: Group by clause doubt - Can you please suggest me the query to this problem??? [message #630282 is a reply to message #630257] Wed, 24 December 2014 01:37 Go to previous messageGo to next message
Michel Cadot
Messages: 68617
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

Please read OraFAQ Forum Guide and How to use [code] tags and make your code easier to read.
Also always post your Oracle version, with 4 decimals.

With any SQL or PL/SQL question, please, Post a working Test case: create table (including all constraints) and insert statements along with the result you want with these data then we will be able work with your table and data. Explain with words and sentences the rules that lead to this result.

Re: Group by clause doubt - Can you please suggest me the query to this problem??? [message #630312 is a reply to message #630282] Wed, 24 December 2014 06:30 Go to previous messageGo to next message
Flyby
Messages: 188
Registered: March 2011
Location: Belgium
Senior Member
something like
select departmenthead.name, departmentheadcount.headcount
from
(
 select department.dept_id,count(*) from department
 inner join employees 
 on department.dept_id=employees.dept_id 
 group by department.dept_id
) departmentheadcount
inner join departments
 on departments.dept_id=departmentheadcount.dept_id
join employees departmenthead
 on departments.dept_head_id=departmenthead.emp_id

Re: Group by clause doubt - Can you please suggest me the query to this problem??? [message #632173 is a reply to message #630312] Mon, 26 January 2015 14:08 Go to previous messageGo to next message
ravikanth_b
Messages: 42
Registered: November 2007
Location: Bay Area, CA
Member
Try this:
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);

select count(*),dept_id
from emp 
group by dept_id;

Re: Group by clause doubt - Can you please suggest me the query to this problem??? [message #632174 is a reply to message #630257] Mon, 26 January 2015 18:02 Go to previous messageGo to next message
Barbara Boehmer
Messages: 9077
Registered: November 2002
Location: California, USA
Senior Member
PHCR,

There appears to be something wrong with your data. You have department heads who are members of some other department, not the one they are the head of. This is why the queries are not producing the results that you expect.
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 Go to previous messageGo to next message
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);
Re: Group by clause doubt - Can you please suggest me the query to this problem??? [message #632176 is a reply to message #632175] Mon, 26 January 2015 19:35 Go to previous messageGo to next message
BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal
Senior Member
>create table EMP(empid number, name varchar2(50), age number, sal number, dept_id number);
It is unwise to ever store a computed value like AGE within any fixed table.
In 367 days or sooner every value in this column will be incorrect. What should be stored in this situation instead is Date_Of_Birth
Re: Group by clause doubt - Can you please suggest me the query to this problem??? [message #632221 is a reply to message #632176] Tue, 27 January 2015 12:01 Go to previous message
ravikanth_b
Messages: 42
Registered: November 2007
Location: Bay Area, CA
Member
Totally agree about the age. It should be DOB DATE period! But that column has nothing to do with the requested solution. I just created my own table sans DDL from original poster.
Previous Topic: Need sql script or proceedure
Next Topic: unknown procedure or function
Goto Forum:
  


Current Time: Mon Mar 18 21:44:31 CDT 2024