SQL aggregate functions in case and then use tips

Source: Internet
Author: User

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

Contact Us

The content source of this page is from Internet, which doesn't represent Alibaba Cloud's opinion; products and services mentioned on that page don't have any relationship with Alibaba Cloud. If the content of the page makes you feel confusing, please write us an email, we will handle the problem within 5 days after receiving your email.

If you find any instances of plagiarism from the community, please send an email to: info-contact@alibabacloud.com and provide relevant evidence. A staff member will contact you within 5 working days.

A Free Trial That Lets You Build Big!

Start building with 50+ products and up to 12 months usage for Elastic Compute Service

  • Sales Support

    1 on 1 presale consultation

  • After-Sales Support

    24/7 Technical Support 6 Free Tickets per Quarter Faster Response

  • Alibaba Cloud offers highly flexible support services tailored to meet your exact needs.