Creating multiple columns in SQL

Question

SELECT 
    SUM([FinalECL]) + SUM([EarlyCorrections]) AS [CollectiveProvisions],
    CASE
        WHEN [Currency] = 'EUR' THEN [CollectiveProvisions]
        WHEN [Currency] = 'DKK' THEN [CollectiveProvisions]*0.13
        WHEN [Currency] = 'NOK' THEN [CollectiveProvisions]*0.1
        ELSE [CollectiveProvisions]*0.094
    END AS [CollProv_New]
FROM
    CollectiveProvision_Q22022
WHERE 
    [MONTH] IN ('202204') 

But when I run the query, it shows this error message:

Invalid column name

How could I fix this bug? [FinalECL] and [EarlyCorrections] are existing columns in the CollectiveProvision_Q22022 table.

Thank you in advance!

Answer 1

This might not be the best solution but it appears to be a working one:

SELECT SUM(CollProv_New) FROM (

SELECT
CASE
    WHEN Currency = 'EUR' THEN CollectiveProvisions*1.0
    WHEN Currency = 'DKK' THEN CollectiveProvisions*0.13
    WHEN Currency = 'NOK' THEN CollectiveProvisions*0.1
    ELSE CollectiveProvisions*0.094
END AS CollProv_New FROM (


SELECT SUM(FinalECL) + SUM(EarlyCorrections) AS CollectiveProvisions, Currency
FROM CollectiveProvision_Q22022
WHERE MONTH in ('202204')
GROUP BY CURRENCY


) as T ) as T2