Why no results from this query?

Question

This is my SQL query and 2 calculations, VAT and Total Cost no longer work. I have tried several adjustments, like adding or removing brackets, but nothing I have done works.

SELECT 
    "ID1", 
    "Date", 
    "KWH Used", 
    "Unit Cost", 
    "Standing Charge", 
    "No Days", 
    "KWH Used" * "Unit Cost" / 100 AS "Electric Cost", 
    "EPG", 
    ( "Electric Cost" ) - ( "EPG" ) AS "Cost after EPG", 
    "No Days" * "Standing Charge" / 100 AS "Total Standing Charge", 
    ( ( "Electric Cost" ) + ( "No Days" ) * ( "Standing Charge" ) / 100 ) * 0.05 AS "VAT", 
    ( "Electric Cost" ) - ( "EPG" ) + ( "No Days" ) * ( "Standing Charge" ) / 100 + ( ( "Electric Cost" ) - ( "EPG" ) + ( "No Days" ) * ( "Standing Charge" ) / 100 ) * 0.05 AS "Total Cost", 
    ( ( "Electric Cost" ) + ( "No Days" ) * ( "Standing Charge" ) / 100 ) * 0.05 AS "Total Cost" 
FROM "EONElectric"

I tried removing brackets and enclosing each part in brackets, reducing or removing a calculation, ie *0.05. I was hoping for some kind of figure in the relevant box

Answer 1

In SQL, you can't reference an alias in the same SELECT statement where it's defined. However, you can use a subquery or a common table expression (CTE) to achieve the desired result.

Here's an example using a subquery:

SELECT 
    "ID1", 
    "Date", 
    "KWH Used", 
    "Unit Cost", 
    "Standing Charge", 
    "No Days", 
    "Electric Cost", 
    "EPG", 
    ("Electric Cost" - "EPG") AS "Cost after EPG", 
    "No Days" * "Standing Charge" / 100 AS "Total Standing Charge", 
    ("Electric Cost" + "No Days" * "Standing Charge" / 100) * 0.05 AS "VAT", 
    ("Electric Cost" - "EPG") + "No Days" * "Standing Charge" / 100 + (("Electric Cost" + "No Days" * "Standing Charge" / 100) * 0.05) AS "Total Cost" 
FROM (
    SELECT 
        "ID1", 
        "Date", 
        "KWH Used", 
        "Unit Cost", 
        "Standing Charge", 
        "No Days", 
        "KWH Used" * "Unit Cost" / 100 AS "Electric Cost", 
        "EPG"
    FROM "EONElectric"
) AS subquery;

In this example, I introduced a subquery that calculates the "Electric Cost" separately, and then I reference it in the main query. This way, you avoid referencing an alias in the same SELECT statement where it's defined.

Please replace the subquery content with your actual calculations.