sql filtering results of alias column

Question

How can we filter for an Alias name? For example, in query below, for alias OLB, if we want to say WHERE OLB > 0

SELECT
loan.lnr
,loan.lamount - SUM (memrepay.mprinc) AS OLB
,lstatus

FROM
 loan
  LEFT OUTER JOIN memrepay
  ON loan.lnr = memrepay.lnr

WHERE
loan.tstart >= N'2013-07-07T14:00:00' 

GROUP BY loan.lnr, lamount, lstatus

Order By lnr

@jarlh used Group BY to get total of mprinc values in all rows. — sqluser, Aug 10 '15 at 18:02
@Indian - sorry didn't notice this question was asked before. — sqluser, Aug 10 '15 at 18:06

score 5 · Accepted Answer · answered Aug 10 '15 at 12:22

Use Derived table

select t.* from
(
SELECT
loan.lnr
,loan.lamount - SUM (memrepay.mprinc) AS OLB
,lstatus

FROM
 loan
  LEFT OUTER JOIN memrepay
  ON loan.lnr = memrepay.lnr

WHERE
loan.tstart >= N'2013-07-07T14:00:00' 

GROUP BY loan.lnr, lamount, lstatus
) as t where OLB>0
Order By lnr

or use HAVING clause and use the expression

SELECT
loan.lnr
,loan.lamount - SUM (memrepay.mprinc) AS OLB
,lstatus

FROM
 loan
  LEFT OUTER JOIN memrepay
  ON loan.lnr = memrepay.lnr
WHERE
loan.tstart >= N'2013-07-07T14:00:00' 

GROUP BY loan.lnr, lamount, lstatus
HAVING loan.lamount - SUM (memrepay.mprinc) >0
Order By lnr

sql filtering results of alias column

1 Answers1