Case Function Returns Null-CodePudding

I have the following input data(A sample only):

ListID	date	Value
0	2022-10-17	0
1	2022-10-17	43.050430504
3	2022-10-17	40.000000000
4	2022-10-17	38.636363636
5	2022-10-17	20.714285714

I am little bit confused about two below query results.

First Query:

SELECT
    ListID,
    CASE 
        WHEN date>'2022-07-22' 
            THEN avg(value)
        ELSE NULL
    END AS 'Value_Before_Rate_Change'
FROM 
    TB01 where date like '2022%' and ListID=1;

Output first query:

Value_Before_Rate_Change
NULL

Second Query

select avg(value)  
from TB01 
where date like '2022%' 
  and ListID=1 
  and date>'2022-07-22';

Output second query:

avg(value)
57.773696518595

Can someone show me why I am always getting NULL as an result when I use CASE.

Update:

I used below group by as well. But same result

SELECT
        ListID,
        CASE 
            WHEN date>'2022-07-22' 
                THEN avg(value)
            ELSE NULL
        END AS 'Value_Before_Rate_Change'
    FROM 
        TB01 where date like '2022%' and ListID=1 group by ListID;

CodePudding user response：

select   listID
        ,avg(case when date > '2022-07-22' then value end) as Value_Before_Rate_Change
from     t
group by listID

listID	Value_Before_Rate_Change
0	0.0000000000000000
1	43.0504305040000000
3	40.0000000000000000
4	38.6363636360000000
5	20.7142857140000000

Fiddle