SQL aggregate functions in case and then use tips

SQL aggregate function in case and then use tip font: [Increase decrease] type: Reprint

We all know that when the case is used in SQL, then the information in the database is converted, such as select (Case sex, 0 Then ' men ' else ' end) as sex from Studentinfo

So what does it do in the aggregate function?

Suppose the database has a table named student.

If you want to now according to this table, the number of men and women in Jiangxi Province, Guangdong Province, the number of male men and women in Zhejiang province How to write SQL statements? That is to generate the following result table

The answer is: Select Sex, Count (case province when ' Guangdong ' then ' Guangdong Province ' end ' as Guangdong Province, Count (case province when ' Jiangxi ' then ' Jiangxi ' end) As Jiangxi Province, Count (case province when ' Zhejiang ' then ' Zhejiang Province ' end) as Zhejiang from student group by sex

The count () function counts the number of rows in a row based on a given range and group by (statistically)

We take a step-by-step understanding of the above statement

1. Select sex from student (query data table for the number of men and women in existence)

2.select Sex, COUNT (*) as num from student GROUP by sex (query table for men and women)

3.select Sex, Province, Count (*) as num from student GROUP by Sex,province (for the number of men and women in each province)

The point is, if I change the * number in count (*) to any column name? What happens if Count (province)?

4.select Sex, Province, Count (province) as Num from student GROUP by Sex,province (for the number of men and women in each province)

The result is the same: it means that the change is the same. Also count (province) is equivalent to count (case province when ' Zhejiang ' then ' Zhejiang ' else province end)

But if we narrow it down to count (case province ' Zhejiang ' then ' Zhejiang Province ' end) then look below

5.select Sex, Province, Count (case province when ' Zhejiang ' then ' Zhejiang Province ' end ' as num from student GROUP by sex,province

That is, the number of men and women is limited in Zhejiang Province to streamline the following

6.select Sex, Count (case province when ' Zhejiang ' then ' Zhejiang Province ' end) as Zhejiang from student group by sex

It's close to our request, now just add a few more fields.

7.select Sex, Count (case province when ' Guangdong ' then ' Guangdong Province ' end ' as Guangdong Province, Count (case province when ' Jiangxi ' then ' Jiangxi ' end) as Jiangxi, Count (case province when ' Zhejiang ' then ' Zhejiang Province ' end) as Zhejiang from student group by sex

Summary: Of course there are many ways to achieve a number of sub-query stitching up is not understandable. I'm just a thought.

Add: Case and then knowledge point

(1) Select (Case province when ' Zhejiang ' then ' Zhejiang ' when ' Jiangxi province ' then ' Jiangxi ' end) as province from student

If the default range is empty if it is not fully enclosed, Guangdong province is empty

(2) Select (Case province when ' Zhejiang ' then ' Zhejiang ' when ' Jiangxi ' then ' Jiangxi ' else Province end ') as province from student

SQL aggregate functions in case and then use tips