Add Infamous Condition to SQL Query

I have this simple table named Transaction_Body

Please notice that I have some values < 0

I have this simple query here

select 
    SUM(T.totalcost) / SUM(T.ItemAmount) as AVG
FROM Transaction_Body T
Where T.ItemsID = 1 and T.DateX <='2023-06-07' 
group by T.ItemsID

This code gives me the average but I want to add a condition beside these conditions up

(( If ‘ItemsAmount’ < 0 in ‘DateX’ = ‘2023-06-07’ Just ignore it from calculation ))

>Solution :

Try the below query: –

SELECT 
    SUM(CASE WHEN T.ItemAmount < 0 AND T.DateX = '2023-06-07' THEN 0 ELSE T.totalcost END) /
    SUM(CASE WHEN T.ItemAmount < 0 AND T.DateX = '2023-06-07' THEN 0 ELSE T.ItemAmount END) AS AVG
FROM Transaction_Body T
WHERE T.ItemsID = 1 AND T.DateX <= '2023-06-07'
GROUP BY T.ItemsID;