The following table TEST:

  category | commdity | price
 ------------+------------+-------
  Clothing | T-shirt | 1000
  Office supplies | puncher | 500
  Kitchen utensils | kitchen knife | 3000
  Kitchenware | Pressure cooker | 6800
  Kitchenware | Fork | 500
  Kitchenware | Cleaning the Vegetable Board | 880
  Office Supplies | Ball Pen | 100
  Clothing | Sports T-shirt | 4000

If I want to show the goods in three grades according to the price below 1000 yuan, 1000-3000 yuan and 3000 yuan, the correct statement is:

SELECT SUM (CASE WHEN price <= 1000 THEN 1 ELSE 0 END)AS low,
SUM (CASE WHEN price BETWEEN 1001 AND 3000 THEN 1 ELSE 0 END)AS mid,
SUM (CASE WHEN price > 3000 THEN 1 ELSE 0 END)AS high FROM TEST;

Results obtained

  low | mid | high
 -----+-----+------
    5 |   1 |    2

When the sum of the above statement is replaced by count, the result is as follows:

  low | mid | high
 -----+-----+------
    8  |   8   |    8

If only columns are displayed, SELECT COUNT (price) FROM TEST WHERE price <= 1000 can get the correct result:

 low 
 ----- 
  5  

So the result with sum is right.
Why?
The effect of three case statements is the same, in fact, it is similar to adding a field, which satisfies the condition of 1, but does not satisfy the condition of 0, so the effect of sum is to add all 1, that is, the number of records satisfying the condition.

And count, whether it's 1 or 0, counts, so it's 8 entries.

Implementing grouping statistics

For example, I would like to count the total number of male, female, and gender-free users created by each creator in the users table (there is a field creator_id in the data table that represents the creator's ID). The statement is as follows:

select u.creator_id creator ID, 

sum( case u.sex when 1 then 1 else 0 end) Male, 

sum( case u.sex when 2 then 1 else 0 end) Female sex, 

sum( case when u.sex<>1 and u.sex<>2 then 1 else 0 end) Gender is empty 

from users u group by u.creator_id;

Introduction to the usage of Oracle CASE WHEN

1. CASE WHEN expression has two forms

--simple Case function  

CASE sex  
WHEN '1' THEN 'male'  
WHEN '2' THEN 'female'  
ELSE 'Other' END  

--Case Search function  

CASE
WHEN sex = '1' THEN 'male'  
WHEN sex = '2' THEN 'female'  
ELSE 'Other' END  

2. The Use of CASE WHEN in Different Positions in Statements

2.1 USE OF SELECT CASE WHEN

SELECT   grade, COUNT (CASE WHEN sex = 1 THEN 1      /*sex 1For boys,2Female student*/
                       ELSE NULL
                       END) Number of boys,
                COUNT (CASE WHEN sex = 2 THEN 1
                       ELSE NULL
                       END) Female student number
    FROM students GROUP BY grade;

2.3 WHERE CASE WHEN Usage

SELECT T2.*, T1.*
   FROM T1, T2
  WHERE (CASE WHEN T2.COMPARE_TYPE = 'A' AND
                   T1.SOME_TYPE LIKE 'NOTHING%'
                THEN 1
              WHEN T2.COMPARE_TYPE != 'A' AND
                   T1.SOME_TYPE NOT LIKE 'NOTHING%'
                THEN 1
              ELSE 0
           END) = 1

2.4 GROUP BY CASE WHEN USE

SELECT  
CASE WHEN salary <= 500 THEN '1'  
WHEN salary > 500 AND salary <= 600  THEN '2'  
WHEN salary > 600 AND salary <= 800  THEN '3'  
WHEN salary > 800 AND salary <= 1000 THEN '4'  
ELSE NULL END salary_class, -- Alias name
COUNT(*)  
FROM    Table_A  
GROUP BY  
CASE WHEN salary <= 500 THEN '1'  
WHEN salary > 500 AND salary <= 600  THEN '2'  
WHEN salary > 600 AND salary <= 800  THEN '3'  
WHEN salary > 800 AND salary <= 1000 THEN '4'  
ELSE NULL END;  

3. Other implementations of IF-THEN-ELSE

3.1 DECODE() function

select decode(sex, 'M', 'Male', 'F', 'Female', 'Unknown')
from   employees;

It seems that only Oracle provides this function, and it does not support ANSI SQL, nor does CASE WHEN have clear grammar, which is not recommended by individuals.

3.2 Special Implementation in WHERE

SELECT T2.*, T1.*
   FROM T1, T2
  WHERE (T2.COMPARE_TYPE = 'A' AND T1.SOME_TYPE LIKE 'NOTHING%')
         OR
        (T2.COMPARE_TYPE != 'A' AND T1.SOME_TYPE NOT LIKE 'NOTHING%')

This method is also used under special circumstances. We should pay more attention to logic and make no mistakes.

Two usages of when then else and decode in oracle, null

(
  CASE
    WHEN T2.BLESSNUM IS NULL
    THEN 0
    ELSE T2.BLESSNUM

  END) AS BLESSNUM

perhaps

ENCODE( T2.BLESSNUM， NULL，0，T2.BLESSNUM) AS BLESSNUM
ENCODE(t.status,'C','Approval and approval','P','Approval','N','Not submitted','D','Examination and approval failed')as status

The two usages are generally null for results that are not involved, and determine whether a result value is null or is not null instead of = null or = = null.