PostgreSQL Performance – Using Previous Column Alias to Calculate Sum

performancepostgresqlpostgresql-performance

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 < '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 >= '2018-01-01 00:00:00'
            and dateinvent <= '2019-12-31 00:00:00'
            and qty>0
            ) as inputQty,
        (select sum(qty) from inventtrans
            where itemid = i.itemid
            and batchno = i.batchno
            and inventlocationid = 'locationId'
            and dateinvent >= '2018-01-01 00:00:00'
            and dateinvent <= '2019-12-31 00:00:00'
            and qty<0
            )as outputQty ,
        (
             COALESCE((select sum(qty) from inventtrans
                where itemid = i.itemid
                and batchno = i.batchno
                and inventlocationid = 'locationId'
                and dateinvent < '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 >= '2018-01-01 00:00:00'
                and dateinvent <= '2019-12-31 00:00:00'
                and qty>0),0)
            +
             COALESCE((select sum(qty) from inventtrans
                where itemid = i.itemid
                and batchno = i.batchno
                and inventlocationid = 'locationId'
                and dateinvent >= '2018-01-01 00:00:00'
                and dateinvent <= '2019-12-31 00:00:00'
                and qty<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 < '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 >= '2018-01-01 00:00:00'
        and dateinvent <= '2019-12-31 00:00:00'
        and qty>0
      ) as inputQty,
      (select sum(qty) from inventtrans
          where itemid = i.itemid
          and batchno = i.batchno
          and inventlocationid = 'locationId'
          and dateinvent >= '2018-01-01 00:00:00'
          and dateinvent <= '2019-12-31 00:00:00'
          and qty<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?

Best Answer

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.

Related Question