Results 1 to 3 of 3
  1. #1
    Join Date
    Jan 2012
    Posts
    20

    Unanswered: max and group_by

    Hi, I am using DB2 9.7.0 LUW and am quite stumped at this seemingly simple task:

    Let's say I have a table called EMPLOYEE

    Code:
    create table employee (
    	id integer not null generated always as identity,
    	name varchar(20) not null,
    	dept varchar(20) not null,
    	gender char(1) not null
    );
    
    insert into employee (name, dept, gender) values ('Andy', 'accounting', 'M');
    insert into employee (name, dept, gender) values ('Bertha', 'security', 'F');
    insert into employee (name, dept, gender) values ('Cathy', 'accounting', 'F');
    insert into employee (name, dept, gender) values ('David', 'marketing', 'M');
    insert into employee (name, dept, gender) values ('Emma', 'accounting', 'F');
    insert into employee (name, dept, gender) values ('Frank', 'security', 'M');
    insert into employee (name, dept, gender) values ('George', 'security', 'M');
    insert into employee (name, dept, gender) values ('Hilda', 'accounting', 'F');
    insert into employee (name, dept, gender) values ('Ian', 'marketing', 'M');
    insert into employee (name, dept, gender) values ('Jason', 'marketing', 'M');
    insert into employee (name, dept, gender) values ('Kay', 'marketing', 'F');
    insert into employee (name, dept, gender) values ('Larry', 'security', 'M');
    insert into employee (name, dept, gender) values ('Mandy', 'security', 'F');
    insert into employee (name, dept, gender) values ('Nancy', 'security', 'F');
    insert into employee (name, dept, gender) values ('Ophelia', 'security', 'F');
    insert into employee (name, dept, gender) values ('Percy', 'accounting', 'M');
    insert into employee (name, dept, gender) values ('Quincy', 'marketing', 'M');
    insert into employee (name, dept, gender) values ('Rhonda', 'marketing', 'F');
    insert into employee (name, dept, gender) values ('Samantha', 'marketing', 'F');
    insert into employee (name, dept, gender) values ('Tammy', 'security', 'F');
    insert into employee (name, dept, gender) values ('Ulrich', 'accounting', 'M');
    insert into employee (name, dept, gender) values ('Vanessa', 'accounting', 'F');
    insert into employee (name, dept, gender) values ('Wanda', 'accounting', 'F');
    insert into employee (name, dept, gender) values ('Xena', 'accounting', 'F');
    insert into employee (name, dept, gender) values ('Yolanda', 'security', 'F');
    insert into employee (name, dept, gender) values ('Zachary', 'marketing', 'M');
    Database design aside, how do I find out which gender
    makes up the majority of each department?

    Code:
    select dept, gender, count(*) as cnt from employee group by dept, gender
    would result in:

    Code:
    accounting	F	6
    accounting	M	3
    marketing	F	3
    marketing	M	5
    security	F	6
    security	M	3
    What I want is:

    Code:
    accounting	F	6
    marketing	M	5
    security	F	6
    I thought I could do it with:

    Code:
    select dept, gender, max(cnt) from (
    	select dept, gender, count(*) as cnt
    	from employee
    	group by dept, gender
    ) group by dept, gender
    but that query gave the same result as above.

    Any takers?

    Thanks,
    Cauliflower

  2. #2
    Join Date
    Nov 2011
    Posts
    334
    plz try:
    Code:
    select dept,gender,cnt from (
    select dept, gender, cnt ,
             row_number() over partition by( dept,gender order by cnt desc) as rn
     from (
    	select dept, gender, count(*) as cnt
    	from employee
    	group by dept, gender
    )  )
    where rn = 1

  3. #3
    Join Date
    Jan 2012
    Posts
    20
    Hey, thanks!

    I modified the statement a bit and it worked:

    Code:
    select dept, gender, cnt from (
    	select dept, gender, cnt, row_number() over (partition by dept order by cnt desc) as rn
    	from (
    		select dept, gender, count(*) as cnt
    		from employee
    		group by dept, gender
    	)
    ) where rn = 1

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •