Using outer alias in a subquery
The two nested selects in your query are called derived tables. A derived table is not meant to be correlated with other datasets participating in the query, hence outer references to them in the nested query are not allowed.

One way to resolve the issue is to rewrite your query so as to move the offending select to the context where correlation is allowed. In your case you can move the offending subquery to the SELECT clause:

select    t.number,
          DATE(p.date),
          ti.total 'total',
          SUM(p.amount) 'paid',
          ti.total - (select sum(inner_p.amount)
                      from     payments inner_p
                      where    inner_p.transaction_id = p.transaction_id
                      and      inner_p.date &lt;= p.date
                     ) 'balance'
from      payments p
left join transactions t
on        p.transaction_id = t.id
left join (
          select   inner_ti.transaction_id, 
                   sum((inner_ti.price - inner_ti.discount) * inner_ti.quantity)  'total'
          from     transaction_items inner_ti
          group by inner_ti.transaction_id
          ) ti 
on        t.id = ti.transaction_id
group by  t.number, DATE(p.date), ti.total, 'balance'
order by  DATE(p.date) ASC;


rextester here



For the sake of completeness, the SQL standard actually has syntax that allows correlation for derived tables. It is called lateral join. From the syntactical point of view, it looks almost exactly like a normal join, you just need to add the LATERAL keyword after JOIN:

…
left join lateral (
    select inner_p.transaction_id, sum(inner_p.amount) 'total'
    from payments inner_p
    where inner_p.date &lt;= p.date -- this outer reference would be valid
    group by inner_p.transaction_id
) paid on t.id = paid.transaction_id
…


The added keyword makes all the difference, as only with that keyword a nested query is permitted to reference other datasets in the same FROM clause (to the left of the most recent JOIN keyword).

Lateral joins are currently supported by PostgreSQL and Oracle. A similar concept with a slightly different (and less flexible) syntax is also supported by SQL Server. As you may have guessed, MySQL does not currently support anything of the sort.