CODEWITHSUNDEEP
mysql
Arifur
Arifur

Reputation: 1

Unknown column 'column_name' in 'on clause' in mysql

I have two table like bellow

CREATE TABLE "accounts" (
    "name"      TEXT,
    "number"    INTEGER,
    "normal"    INTEGER
)

and

CREATE TABLE "transactions"
  (
     "id"        INTEGER, 
     "date"      TEXT,
     "amount"    REAL,
     "account"   INTEGER,
     "direction" INTEGER
  )

when I run this query it shown Unknown column 'a' in 'on clause'

Query is -

select
   ((account / 100) * 100) as a,
   name,
   sum(amount * direction * normal) as balance
 from
   transactions
   left join accounts on a = accounts.number
 group by
   name
order by
  a,
  name;

I tried a lot by changing queries and unable to solve that.

Upvotes: 0

Views: 37

Answers (1)

Barmar
Barmar

Reputation: 780889

You can't refer to a column alias in the ON or WHERE clauses in the same query. So you need to repeat the expression.

select
   ((account / 100) * 100) as a,
   name,
   sum(amount * direction * normal) as balance
 from
   transactions
   left join accounts on a = ((account / 100) * 100)
 group by
   name
order by
  a,
  name;

Upvotes: 1

Related Questions