Oracle Count in Group by

Question

For example ,I have a table represents all teachers in my school including his/her name , sex and the class he/she belongs to.I want to know , for each class, how many males and how many females teachers in a SQL statement.

Abosolutely , each teacher can only belong to one class!

COLUMN  TYPE       DESCRIPTION
CLASS   NUMBER     the class number,e.g. 0,1,2,3 ...
SEX     NUMBER     teacher sex, 0 represents male and 1 represents female.
TEACHER VARCHAR    teacher name like Lily,Lucy

More deep:

I have a table represents all teachers in my school including his/her name , age and the class he/she belongs to.I want to know , for each class, how many teachers are younger than 20 and how many teachers are older that 20 at the same time in a SQL statement.

COLUMN  TYPE       DESCRIPTION
CLASS   NUMBER     the class number,e.g. 0,1,2,3 ...
AGE     NUMBER     teacher ages.
TEACHER VARCHAR    teacher name like Lily,Lucy

Add table definitions, sample data, expected result and your current query! (I.e. help us to help you!) — jarlh
– jarlh, Commented May 8, 2015 at 8:34
Can a teacher only belong to one class? If not, I'd say you need another table to store teacher/class combinations. — jarlh
– jarlh, Commented May 8, 2015 at 8:46
of course, each teacher can only belong to only one class.@jarlh — wuchang
– wuchang, Commented May 8, 2015 at 8:53

Hotdin Gurning · Accepted Answer · 2015-05-08 08:51:27Z

1

Try this :

SELECT CLASS, SUM(MALE) AS MALE, SUM(FEMALE) AS FEMALE,
SUM(YOUNGER) AS YOUNGER, SUM(OLDER) AS OLDER
FROM(
SELECT CLASS,
(CASE WHEN SEX = 0 THEN 1 ELSE 0 END) AS MALE,
(CASE WHEN SEX = 1 THEN 1 ELSE 0 END) AS FEMALE,
(CASE WHEN AGE < 20 THEN 1 ELSE 0 END) AS YOUNGER,
(CASE WHEN AGE >= 20 THEN 1 ELSE 0 END) AS OLDER
FROM YOURTABLE
) AS T
GROUP BY CLASS

edited May 8, 2015 at 8:51

answered May 8, 2015 at 8:45

Hotdin Gurning

1,8193 gold badges16 silver badges24 bronze badges

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

Comments

Maheswaran Ravisankar · Accepted Answer · 2015-05-08 11:44:12Z

1

SELECT CLASS,
       COUNT(DECODE(SEX,'0','MALE')) AS MALE_TEACHERS,
       COUNT(DECODE(SEX,'1','FEMALE')) AS FEMALE_TEACHERS,
       COUNT(CASE WHEN AGE < 20 THEN 1 ELSE NULL END) AS "<20",
       COUNT(CASE WHEN AGE >= 20 THEN 1 ELSE NULL END) AS ">20"
FROM YOURTABLE
GROUP BY CLASS

edited May 8, 2015 at 11:44

answered May 8, 2015 at 8:43

Maheswaran Ravisankar

17.9k6 gold badges50 silver badges69 bronze badges

1 Comment

Rajesh Over a year ago

Query need to be modified to handle age = 20 matches [CASE WHEN AGE >= 20 THEN 1 ELSE NULL END]

Narashi Hadiya · Accepted Answer · 2015-05-08 08:47:33Z

0

Try This....

CREATE TABLE #Class
    ([Class] int,[sex] bit)
;

INSERT INTO #Class
    ([Class], [sex])
VALUES
    (1, 1),
    (1,1),
    (1,0),
    (1,0),
    (2,0),
    (2,0),
    (2,1),
    (2,1),
    (2,1),
    (1,1)
;


 select class, case when sex = 0 then 'male' else 'Female' End as sex ,Count(Sex) as count1 from #Class  group by class,sex
--drop table #Class

answered May 8, 2015 at 8:47

Narashi Hadiya

891 silver badge11 bronze badges

1 Comment

user330315 Over a year ago

Your sample setup is invalid for Oracle.

Matt · Accepted Answer · 2015-05-08 08:49:07Z

0

Group BY class and sex

SELECT CLASS, COUNT(SEX)
FROM TeacherTable
GROUP BY CLASS, SEX

SQL FIDDLE: http://sqlfiddle.com/#!4/60ddc/10/0

edited May 8, 2015 at 8:49

answered May 8, 2015 at 8:42

Matt

15.1k28 gold badges109 silver badges161 bronze badges

3 Comments

Rajesh Over a year ago

How this query will give - for each class, how many male teachers and how many female teachers

Matt Over a year ago

Because it groups by class AND sex, SQL FIDDLE added to show output

Rajesh Over a year ago

I have seen query result, but how you will understand which one are Male, Female.

Lalit Kumar B · Accepted Answer · 2015-05-08 09:04:30Z

You could use CASE/DECODE to differentiate the gender and GROUP BY to get the count.

Setup

SQL> CREATE TABLE t
  2      (class number, sex number, teacher varchar2(1));

Table created.

SQL> INSERT ALL
  2      INTO t (class, sex, teacher)
  3           VALUES (1, 1, 'a')
  4      INTO t (class, sex, teacher)
  5           VALUES (1, 0, 'b')
  6      INTO t (class, sex, teacher)
  7           VALUES (1, 0, 'c')
  8      INTO t (class, sex, teacher)
  9           VALUES (2, 1, 'd')
 10      INTO t (class, sex, teacher)
 11           VALUES (2, 1, 'e')
 12      INTO t (class, sex, teacher)
 13           VALUES (2, 0, 'f')
 14  SELECT * FROM dual;

6 rows created.

SQL> SELECT * FROM t;

     CLASS        SEX T
---------- ---------- -
         1          1 a
         1          0 b
         1          0 c
         2          1 d
         2          1 e
         2          0 f

6 rows selected.

Query

SQL> SELECT class,
  2    CASE
  3      WHEN sex = 0
  4      THEN 'male'
  5      ELSE 'Female'
  6    END AS sex,
  7    COUNT(sex)
  8  FROM t
  9  GROUP BY CLASS,
 10    sex
 11  ORDER BY class;

     CLASS SEX    COUNT(SEX)
---------- ------ ----------
         1 male            2
         1 Female          1
         2 male            1
         2 Female          2

Collectives™ on Stack Overflow

Oracle Count in Group by

5 Answers 5

Comments

1 Comment

1 Comment

3 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

5 Answers 5

Comments

1 Comment

1 Comment

3 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related