Add previous column alias to calculate sum/total of last column I have a query which I need to optimize. It is using 3 subqueries 2 times each. Below is the query. 

select i.itemid, b.itemname, i.batchno,
        (select sum(qty) from inventtrans
            where itemid = i.itemid
            and batchno = i.batchno
            and inventlocationid = 'locationId'
            and dateinvent &lt; '2018-12-26 00:00:00') as openingBal ,
        (select sum(qty) from inventtrans
            where itemid = i.itemid
            and batchno = i.batchno
            and inventlocationid = 'locationId'
            and dateinvent &gt;= '2018-01-01 00:00:00'
            and dateinvent &lt;= '2019-12-31 00:00:00'
            and qty&gt;0
            ) as inputQty,
        (select sum(qty) from inventtrans
            where itemid = i.itemid
            and batchno = i.batchno
            and inventlocationid = 'locationId'
            and dateinvent &gt;= '2018-01-01 00:00:00'
            and dateinvent &lt;= '2019-12-31 00:00:00'
            and qty&lt;0
            )as outputQty ,
        (
             COALESCE((select sum(qty) from inventtrans
                where itemid = i.itemid
                and batchno = i.batchno
                and inventlocationid = 'locationId'
                and dateinvent &lt; '2018-12-26 00:00:00'),0)
            +
             COALESCE((select sum(qty) from inventtrans
                where itemid = i.itemid
                and batchno = i.batchno
                and inventlocationid = 'locationId'
                and dateinvent &gt;= '2018-01-01 00:00:00'
                and dateinvent &lt;= '2019-12-31 00:00:00'
                and qty&gt;0),0)
            +
             COALESCE((select sum(qty) from inventtrans
                where itemid = i.itemid
                and batchno = i.batchno
                and inventlocationid = 'locationId'
                and dateinvent &gt;= '2018-01-01 00:00:00'
                and dateinvent &lt;= '2019-12-31 00:00:00'
                and qty&lt;0),0)
        ) as bal,
            i.location,
            -- split_part(i.inventsizeid, '/+[0-9]', 2) as size
            i.inventsizeid as size,
            i.configid as color
from inventtrans i, view_inventbatchtrans b
where
i.batchno = b.batchid
group by i.itemid,b.itemname,i.batchno,i.qty,i."location",i.inventsizeid,i.configid;


check the query for openingBal, inputQty, outputQt. 
I need to add these 3 columns to get bal column. Here is the query which I optimized. 

select i.itemid, b.itemname, i.batchno, i.openingBal, i.inputQty, i.outputQty, i.bal, i.location, i.inventsizeid as size, i.configid as color
from (select i.*,
  (select sum(qty) from inventtrans
    where itemid = i.itemid
    and batchno = i.batchno
    and inventlocationid = 'locationId'
    and dateinvent &lt; '2018-12-26 00:00:00') as openingBal,
    (select sum(qty) from inventtrans
        where itemid = i.itemid
        and batchno = i.batchno
        and inventlocationid = 'locationId'
        and dateinvent &gt;= '2018-01-01 00:00:00'
        and dateinvent &lt;= '2019-12-31 00:00:00'
        and qty&gt;0
      ) as inputQty,
      (select sum(qty) from inventtrans
          where itemid = i.itemid
          and batchno = i.batchno
          and inventlocationid = 'locationId'
          and dateinvent &gt;= '2018-01-01 00:00:00'
          and dateinvent &lt;= '2019-12-31 00:00:00'
          and qty&lt;0
        )as outputQty, (COALESCE(i.openingBal, 0) + COALESCE(i.inputQty, 0) + COALESCE(i.outputQty, 0)) as bal
      from inventtrans i
    ) i, view_inventbatchtrans b
        where
        i.batchno = b.batchid
        group by i.itemid,b.itemname,i.batchno,i.qty,i."location",i.inventsizeid,i.configid;`
 Where I run optimized query it throwing an error as 



  SQL Error [42703]: ERROR: column i.openingbal does not exist
        Position: 937 which is at `(COALESCE(i.openingBal, 0) + COALESCE(i.inputQty, 0) + COALESCE(i.outputQty, 0)) as bal


Could you tell what is it that I'm doing wrong. Can we use alias column for this purpose?
Aliases are only available in GROUP BY, ORDER BY or HAVING (aside from direct output).
Found this link as helpful : https://stackoverflow.com/questions/3932205/to-calculate-sum-two-alias-named-columns-in-sql?rq=1

Please check it.