Reuse Alias on calculated field

Question

Good afternoon, wonder if anyone could point me in the right direction as I am struggling a little. I have a mysql query that I need to include an alias in a calculated field as such:

 Select tblComms._CommMonth, 
        tblComms._Reference, 
        tblComms._ClientName, 
        tblComms._Premium, 
        tblComms._CommDue, 
        tblComms._Employee_Name, 
        tblCont.Retention, 
        (tblComms._CommDue) * (tblCont.Retention) / 100 As Paid, 
        (tblComms._CommDue) - (Paid) As Payable 
 From tblComms Inner Join dbo_companyref On 
      dbo_companyref._Reference = tblComms._Reference 
      Inner Join tblCont 
      On dbo_companyref._Advisor_Name = tblCont._Employee_Name

This returns an error "Unknown columns 'Paid' in field list", is there any way I can use the Paid alias after its been created at all? I am trying tp roll out a new system which was created in Access & SQL, they simply used saved queries / SPs for that..

Answer 1

Its not allowed. You cannot use the column as an alias when the alias and other column are in the same level of SELECT.

You could have used the alias if it was something like this -

SELECT alias
FROM (SELECT column1 AS alias
      FROM table);

Answer 2

You can use variables in mysql for this stuff:

Select tblComms._CommMonth, 
    tblComms._Reference, 
    tblComms._ClientName, 
    tblComms._Premium, 
    tblComms._CommDue, 
    tblComms._Employee_Name, 
    tblCont.Retention, 
    @Paid := (tblComms._CommDue) * (tblCont.Retention) / 100 As Paid, 
    (tblComms._CommDue) - (@Paid) As Payable From tblComms Inner Join dbo_companyref On 
  dbo_companyref._Reference = tblComms._Reference 
  Inner Join tblCont 
  On dbo_companyref._Advisor_Name = tblCont._Employee_Name