CODEWITHSUNDEEP
mysqlsql
Pathi
Pathi

Reputation: 133

Case Function Returns Null

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;

Upvotes: 1

Views: 39

Answers (1)

DannySlor
DannySlor

Reputation: 4620

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

Upvotes: 1

Related Questions