This article will show you how to use SQL CASE WHEN in detail

This article will show you how to use SQL CASE WHEN in detail

Simple CASE WHEN function:

CASE SCORE WHEN 'A' THEN 'Excellent' ELSE 'Failed' END
CASE SCORE WHEN 'B' THEN 'Good' ELSE 'Failed' END
CASE SCORE WHEN 'C' THEN 'Medium' ELSE 'Failed' END

This is equivalent to using the CASE WHEN conditional expression function:

CASE WHEN SCORE = 'A' THEN 'Excellent'
     WHEN SCORE = 'B' THEN 'Good'
     WHEN SCORE = 'C' THEN 'Medium' ELSE 'Failed' END

The value after THEN should be of the same type as the value after ELSE, otherwise an error will be reported. as follows:

CASE SCORE WHEN 'A' THEN 'Excellent' ELSE 0 END

If the data types of 'Excellent' and 0 are inconsistent, an error will be reported:

[Err] ORA-00932: inconsistent data types: expected CHAR, got NUMBER

The simple CASE WHEN function can only handle some simple business scenarios, while the writing rules of the CASE WHEN conditional expression are more flexible.

CASE WHEN conditional expression function: similar to the IF ELSE statement in JAVA.

Format:

CASE WHEN condition THEN result

[WHEN...THEN...]

ELSE result

END

condition is an expression that returns a Boolean type. If the expression returns true, the entire function returns the value of the corresponding result. If all expressions are false, the value of the result after ElSE is returned. If the ELSE clause is omitted, NULL is returned.

The following are some common scenarios.

Scenario 1: There is a score, score < 60 returns a failed score, score >= 60 returns a passed score, and score >= 80 returns an excellent score

SELECT
    STUDENT_NAME,
    (CASE WHEN score < 60 THEN 'failed'
        WHEN score >= 60 AND score < 80 THEN 'pass'
        WHEN score >= 80 THEN 'Excellent'
        ELSE 'Exception' END) AS REMARK
FROM
    TABLE

Note : If you want to check whether the score is null, WHEN score = null THEN 'absent from the exam', this is an incorrect way of writing. The correct way to write it is:

CASE WHEN score IS NULL THEN 'Absent from the exam' ELSE 'Normal' END

Scenario 2: The teacher needs to count how many boys and girls there are in the class, and how many boys and girls have passed the exam. The teacher needs to use a SQL statement to output the results.

The table structure is as follows: In the STU_SEX field, 0 represents a boy and 1 represents a girl.

STU_CODE STU_NAME STU_SEX STU_SCORE
XM Xiao Ming 0 88
XL Xiaolei 0 55
XF Xiaofeng 0 45
XH Little Red 1 66
XN Xiaoni 1 77
XY Xiao Yi 1 99
SELECT 
	SUM (CASE WHEN STU_SEX = 0 THEN 1 ELSE 0 END) AS MALE_COUNT,
	SUM (CASE WHEN STU_SEX = 1 THEN 1 ELSE 0 END) AS FEMALE_COUNT,
	SUM (CASE WHEN STU_SCORE >= 60 AND STU_SEX = 0 THEN 1 ELSE 0 END) AS MALE_PASS,
	SUM (CASE WHEN STU_SCORE >= 60 AND STU_SEX = 1 THEN 1 ELSE 0 END) AS FEMALE_PASS
FROM 
	THTF_STUDENTS

The output is as follows:

MALE_COUNT FEMALE_COUNT MALE_PASS FEMALE_PASS
3 3 1 3

Scenario 3: Classic row-to-column conversion and statistical analysis with aggregate functions

Now it is required to count the total water consumption, electricity consumption, and heat consumption of each city, and use a SQL statement to output the results

The energy consumption table is as follows: E_TYPE represents the energy consumption type, 0 represents water consumption, 1 represents electricity consumption, and 2 represents heat consumption

E_CODE E_VALUE E_TYPE
Beijing 28.50 0
Beijing 23.51 1
Beijing 28.12 2
Beijing 12.30 0
Beijing 15.46 1
Shanghai 18.88 0
Shanghai 16.66 1
Shanghai 19.99 0
Shanghai 10.05 0
SELECT 
	E_CODE,
	SUM(CASE WHEN E_TYPE = 0 THEN E_VALUE ELSE 0 END) AS WATER_ENERGY,--Water consumption SUM(CASE WHEN E_TYPE = 1 THEN E_VALUE ELSE 0 END) AS ELE_ENERGY,--Electricity consumption SUM(CASE WHEN E_TYPE = 2 THEN E_VALUE ELSE 0 END) AS HEAT_ENERGY--Heat consumption FROM 
	THTF_ENERGY_TEST
GROUP BY
	E_CODE

The output is as follows :

E_CODE WATER_ENERGY ELE_ENERGY HEAT_ENERGY
Beijing 40.80 38.97 28.12
Shanghai 48.92 16.66 0

Scenario 4: Using subqueries in CASE WHEN

Calculate the electricity cost based on the city’s electricity consumption. Assume that the unit price of electricity consumption is divided into three levels, and use the corresponding prices to calculate the cost according to different energy consumption values.

The price list is as follows:

P_PRICE P_LEVEL P_LIMIT
1.20 0 10
1.70 1 30
2.50 2 50

When the energy consumption value is less than 10, the P_PRICE value when P_LEVEL=0 is used. When the energy consumption value is greater than 10 and less than 30, the P_PRICE value when P_LEVEL=1 is used.

CASE WHEN energy <= (SELECT P_LIMIT FROM TABLE_PRICE WHERE P_LEVEL = 0) THEN (SELECT P_PRICE FROM TABLE_PRICE WHERE P_LEVEL = 0)
    WHEN energy > (SELECT P_LIMIT FROM TABLE_PRICE WHERE P_LEVEL = 0) AND energy <= (SELECT P_LIMIT FROM TABLE_PRICE WHERE P_LEVEL = 1) THEN (SELECT P_PRICE FROM TABLE_PRICE WHERE P_LEVEL = 1)
    WHEN energy > (SELECT P_LIMIT FROM TABLE_PRICE WHERE P_LEVEL = 1) AND energy <= (SELECT P_LIMIT FROM TABLE_PRICE WHERE P_LEVEL = 2) THEN (SELECT P_PRICE FROM TABLE_PRICE WHERE P_LEVEL = 2)

Scenario 5: Combined with the max aggregation function

The CASE WHEN function is simple to use and easy to understand. This article only gives a brief introduction to its use. It is necessary to use it flexibly according to different business scenarios in actual work.

Summarize

This article ends here. I hope it can be helpful to you. I also hope that you can pay more attention to more content on 123WORDPRESS.COM!

You may also be interested in:
  • Example of using judgment statements (IF ELSE/CASE WHEN) in SQL Server
  • SqlServer uses case when to solve multi-condition fuzzy query problems
  • SQL learning: usage of CASE WHEN THEN ELSE END
  • Brief analysis of two methods of SQL statement row and column conversion: case...when and application of pivot function
  • Tips for using case when then in SQL aggregate functions
  • How to use case when syntax in sql
  • Detailed explanation of the specific usage of SQL CASE WHEN

<<:  Summary of things to pay attention to in the footer of a web page

>>:  CSS to achieve Skeleton Screen effect

Recommend

mysql show simple operation example

This article describes the mysql show operation w...

Example of how to reference environment variables in Docker Compose

In a project, you often need to use environment v...

How to write the Nofollow tag and how to use it

The "nofollow" tag was proposed by Goog...

Some functions of using tcpdump to capture packets in the Linux command line

tcpdump is a flexible and powerful packet capture...

Semantic web pages XHTML semantic markup

Another important aspect of separating structure ...

How to get the width and height of the image in WeChat applet

origin Recently, I am working on requirement A, i...

Solutions to MySql crash and service failure to start

I have been in contact with PHP for so long, but ...

How to install Nginx in Docker

Install Nginx on Docker Nginx is a high-performan...

JavaScript recursion detailed

Table of contents 1. What is recursion? 2. Solve ...

How to use Docker to build a tomcat cluster using nginx (with pictures and text)

First, create a tomcat folder. To facilitate the ...

Practice of implementing custom search bar and clearing search events in avue

Table of contents 1. Customize the search bar con...

Detailed explanation of Linux text processing tools

1. Count the number of users whose default shell ...

Summary of DTD usage in HTML

DTD is a set of grammatical rules for markup. It i...

Detailed example of locating and optimizing slow query sql in MySQL

Table of contents 1. How to locate and optimize s...