CODEWITHSUNDEEP
sqloracle-databasecountgroup-by
wuchang
wuchang

Reputation: 3069

Oracle Count in Group by

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

Upvotes: 1

Views: 2362

Answers (5)

Maheswaran Ravisankar
Maheswaran Ravisankar

Reputation: 17920

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

Upvotes: 1

Lalit Kumar B
Lalit Kumar B

Reputation: 49062

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

Upvotes: 0

Hotdin Gurning
Hotdin Gurning

Reputation: 1819

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

Upvotes: 1

Matt
Matt

Reputation: 15061

Group BY class and sex

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

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

Upvotes: 0

Narashi Hadiya
Narashi Hadiya

Reputation: 89

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

Upvotes: 0

Related Questions