DWQA QuestionsCategory: Database[sql] how to count the number of male and female students in each class?
weknow asked 3 months ago

The user table has fields: name, sex, CLassID, such as the following records:
a. Male, 1
b. Male, 1
c. Female, 1
d. Female, 1
e. Male, 2
f. Male, 2
It is required to find out the following records (how many men and women are there in each class):
CLassID male and female
1 2 2
2 1 1
How do I write it?
Just finding out the number of boys in each class would:
Select class, count (*) as’ male ‘from t_ UU where sex=’male’ group by class;,Command:

Select class_ ID, sum (sex = 'male') as male, sum (sex = 'female') as female from student
GROUP BY class_id

Results:

Original data:

select * from student;

,The data is wrong. If the CLassID is 2, there should be only two boys o (╯□ ╯ system) o,My answer is based on the understanding of the landlord. Of course, I am also:
First find out the situation of boys, such as t1:
|class_id|male|
|:—:|:—:|
|1|2|
|2|1|
Then find out the situation of girls, such as t2:

class_id
female

1
2

2
1

Finally, link the two watches:select t1.class_id,male,female from t1,t2 where t1.class_id = t2.class_id
I didn’t try, but I guess it means yes

3 Answers
Honwhy answered 3 months ago

Command:

Select class_ ID, sum (sex = 'male') as male, sum (sex = 'female') as female from student
GROUP BY class_id

Results:

Original data:

select * from student;

weknow replied 3 months ago

Thank you, right

Li Yang answered 3 months ago

The data is wrong. If the CLassID is 2, there should be only two boys o (╯□ ╯ system) o

weknow replied 3 months ago

Oh, I made a mistake, but that’s not the point. The point is how to write SQL statements???????

Willow Hermit answered 3 months ago

My answer is based on the understanding of the landlord. Of course, I am also:
First find out the situation of boys, such as t1:
|class_id|male|
|:—:|:—:|
|1|2|
|2|1|
Then find out the situation of girls, such as t2:

class_id
female

1
2

2
1

Finally, link the two watches:select t1.class_id,male,female from t1,t2 where t1.class_id = t2.class_id
I didn’t try, but I guess it means yes