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

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:

Second Query

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

Output second query:

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

Fiddle