Gusti Aldi Gusti Aldi - 6 months ago 23
MySQL Question

do math in mysql select with as

tried to select table with alias and simple math, tried to call the alias to use function in where query, found error

here's the code

SELECT DISTINCT
p.PONumber,
p.POdate,
p.customername,
p.description,
p.poqty,
SUM(q.deliveryqty) AS TotalQtySpb,
p.poqty-SUM(q.deliveryqty) AS otspo
FROM
tb_po p
WHERE
otspo != ''
INNER JOIN
tb_rls q ON p.PONumber = q.PONumber AND p.description = q.description
GROUP BY
p.PONumber,
p.PODate,
p.customername,
p.description,
p.poqty


got this error :

1064 - You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near 'INNER JOIN



tb_rls q ON p.PONumber = q.PONumber AND p.description = q.descript' at line 13

is that me wrong using 'where' query ?

Answer

It's not possible to reference an expression by the alias assigned in the SELECT list in the WHERE clause of the same SELECT.

But the alias can be referenced in a HAVING clause.

. remove the WHERE clause (which is in the wrong place anyway)

. at the end of the statement following the GROUP BY clause, add

   HAVING otspo != ''