SQL Exercise 1: count the number of male and female students in the class

1. There are the following tables and data:
2 ID name class sex
3 -----------------------------------
4. 1. Class 1 male
5, 2, 2, class 1 male
6, 3, 3, and 2
7, 4, 4, class 1 male
8, 5, 5, class 2, women
9, 6, 6, class 2, women
10 7 boys in class 7 and Class 2
11, 8, 8, class-1 women
12 The following results must be queried using the SELECT statement:
13
Class 14 male and female
15 --------------------------------
16, class 2, class 1, and Class 3
17 Class 1 3 1
18
19
20 Use studb
21 go
22 create table testtable
23 (
24 ID int identity (1, 1) primary key,
25 [name] varchar (10 ),
26 [Class] varchar (10 ),
27 sex char (2)
28)
29
30 Insert testtable values ('zhang 1', 'class 1 ', 'mal ')
31 insert testtable values ('zhang 2', 'class 1 ', 'mal ')
32 insert testtable values ('zhang 3 ', 'second class', 'female ')
33 insert testtable values ('zhang 4', 'class 1 ', 'mal ')
34 insert testtable values ('zhang 5', 'second class', 'female ')
35 insert testtable values ('zhang 6', 'second class', 'female ')
36 insert testtable values ('zhang 7', 'class 2 ', 'mal ')
37 insert testtable values ('zhang 8', 'class 1 ', 'female ')
38
39 select * From testtable
40
41-- Answer 1:
42Select class, count (case when sex = 'male' then 1 end) as male,
43Count (case when sex = 'female' then 1 end)Female
44From testtable group by class
45 -- Answer 2:
46 select class, sum (male) as male, sum (female) as female from (
47 select class as class, count (sex) as male, 0 as female from testtable
48 where sex = 'male'
49 group by class
50 Union
51 select class as class, 0 as male, count (sex) as female from testtable
52 where sex = 'female'
53 group by class
54)
55 group by class

 

This article references from: http://www.cnblogs.com/seerlin/archive/2009/02/17/1392230.html