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

Does this answer your question? [use mysql SUM() in a WHERE clause](https://stackoverflow.com/questions/3284409/use-mysql-sum-in-a-where-clause) — AymDev, Jul 19 '21 at 08:11
Because there was never a column named `TOTAL` in the subquery of `AL` therefore `AL.TOTAL` is unknown. If you want to use `SUM(AL.BEGINING+AL.MUTATION) AS TOTAL` from the outer query, you have to use `HAVING` instead of `WHERE` like lucumt answer below or you can just wrap around the query to become another subquery again then you can use `TOTAL` in `WHERE`. — FanoFN, Jul 19 '21 at 08:23

score 0 · Accepted Answer · answered Jul 19 '21 at 08:19

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

References Outer Query MySQL

1 Answers1