References Outer Query MySQL

Question

i tried use "Total" in outer where, and i got error "Unknown "AL.Total", how to fix it ? here my query :

SELECT 
AL.CODE,
AL.NAME,
SUM(AL.BEGINING+AL.MUTATION) AS TOTAL
from
(SELECT
    ACC.CODE,
    ACC.NAME,
    ACC.BEGINING,
    SUM(JOURNAL.DEBIT-JOURNAL.KREDIT) AS MUTATION
FROM 
    G_JOURNAL INNER JOIN P ON Category.Category= G_JOURNAL.Category
    INNER JOIN JOURNAL ON G_JOURNAL.NOINDEX = JOURNAL.NOINDEX_JOURNAL
    INNER JOIN ACCOUNT ON JOURNAL.CODE = ACC.CODE  
    INNER JOIN SUBCLASS ON SUBCLASS.NOSUBCLASS= ACC.NOSUBCLASS
    INNER JOIN CLASS ON SUBCLASS.NOSUBCLASS = CLASS.NOCLASS
WHERE  
        (MONTH(G_JOURNAL.DATE) <= '12' AND YEAR (G_JOURNAL.DATE<= '2020') 
    AND 
        SUBCLASS.NOSUBCLASS=120
 GROUP BY ACC.NAME) AL
 WHERE AL.TOTAL >0
 GROUP BY AL.NAME
 ORDER BY AL.CODE ASC

Answer 1

Try HAVING TOTAL >0 instead of WHERE AL.TOTAL >0,because TOTAL is not in AL temp table

SELECT 
AL.CODE,
AL.NAME,
SUM(AL.BEGINING+AL.MUTATION) AS TOTAL
from
(SELECT
    ACC.CODE,
    ACC.NAME,
    ACC.BEGINING,
    SUM(JOURNAL.DEBIT-JOURNAL.KREDIT) AS MUTATION
FROM 
    G_JOURNAL INNER JOIN P ON Category.Category= G_JOURNAL.Category
    INNER JOIN JOURNAL ON G_JOURNAL.NOINDEX = JOURNAL.NOINDEX_JOURNAL
    INNER JOIN ACCOUNT ON JOURNAL.CODE = ACC.CODE  
    INNER JOIN SUBCLASS ON SUBCLASS.NOSUBCLASS= ACC.NOSUBCLASS
    INNER JOIN CLASS ON SUBCLASS.NOSUBCLASS = CLASS.NOCLASS
WHERE  
        (MONTH(G_JOURNAL.DATE) <= '12' AND YEAR (G_JOURNAL.DATE<= '2020') 
    AND 
        SUBCLASS.NOSUBCLASS=120
 GROUP BY ACC.NAME) AL
 GROUP BY AL.NAME
 HAVING TOTAL >0
 ORDER BY AL.CODE ASC