how to use Alias Name in where clause in SQL

Question

firstly, I reviewed most of the answers here but I couldn't apply the solution to my case. always it gives me an error.

I need to use the "FinalTotal" Alias name in where clause:

SELECT
        p.ID,
        p.TheName0, 

        (SELECT IFNULL(SUM(att.S_FinalAmount),0) From tbl_groups_classes_att att 
         INNER JOIN tbl_students st 
         ON st.ID = att.StudentID
         INNER JOIN tbl_groups_classes cls
         ON cls.ID = att.ClassID

         WHERE st.ParentID = p.ID 
        and cls.TheDate BETWEEN @Date1 and @Date2 
        and att.TheStatus <> 'N'
        ) as CurrMost,

        (SELECT IFNULL(SUM(att.S_FinalAmount),0) From tbl_groups_classes_att att 
         INNER JOIN tbl_students st 
         ON st.ID = att.StudentID
         INNER JOIN tbl_groups_classes cls
         ON cls.ID = att.ClassID

         WHERE st.ParentID = p.ID and cls.TheDate< @Date1 and att.TheStatus <> 'N'
        ) as PrevMost,

        (SELECT IFNULL(SUM(pay.TheAmount),0) From tbl_parents_payments pay Where p.ID = pay.ParentID
        AND pay.TheDate BETWEEN @Date1 and @Date2
        ) as CurrMadf,

        (SELECT IFNULL(SUM(pay.TheAmount),0) From tbl_parents_payments pay Where p.ID = pay.ParentID
        AND pay.TheDate < @Date1
        ) as PrevMadf,

        (SELECT CurrMost + PrevMost) as AllMost,
        (SELECT CurrMadf + PrevMadf) as AllMadf,
        (SELECT AllMost - AllMadf) AS FinalTotal 

        from tbl_parents p
        
        WHERE FinalTotal <> 0

but I got Error:

#1054 - Unknown column 'FinalTotal' in 'field list'

Just replace `WHERE FinalTotal <> 0` with `WHERE AllMost - AllMadf <> 0`, and `(SELECT AllMost - AllMadf)` with `AllMost - AllMadf` too ... — Barbaros Özhan, Dec 29 '21 at 17:59
The finalTotal _column_ isn't available in the WHERE clause, because (logically) the WHERE clause is evaluated before the column alias is created. MySQL special trick is to use HAVING clause instead. — jarlh, Dec 29 '21 at 17:59

score 0 · Accepted Answer · answered Dec 29 '21 at 18:01

You should use subquery syntax:

SELECT * FROM (
SELECT
    p.ID,
    p.TheName0, 

    (SELECT IFNULL(SUM(att.S_FinalAmount),0) From tbl_groups_classes_att att 
     INNER JOIN tbl_students st 
     ON st.ID = att.StudentID
     INNER JOIN tbl_groups_classes cls
     ON cls.ID = att.ClassID

     WHERE st.ParentID = p.ID 
    and cls.TheDate BETWEEN @Date1 and @Date2 
    and att.TheStatus <> 'N'
    ) as CurrMost,

    (SELECT IFNULL(SUM(att.S_FinalAmount),0) From tbl_groups_classes_att att 
     INNER JOIN tbl_students st 
     ON st.ID = att.StudentID
     INNER JOIN tbl_groups_classes cls
     ON cls.ID = att.ClassID

     WHERE st.ParentID = p.ID and cls.TheDate< @Date1 and att.TheStatus <> 'N'
    ) as PrevMost,

    (SELECT IFNULL(SUM(pay.TheAmount),0) From tbl_parents_payments pay Where p.ID = pay.ParentID
    AND pay.TheDate BETWEEN @Date1 and @Date2
    ) as CurrMadf,

    (SELECT IFNULL(SUM(pay.TheAmount),0) From tbl_parents_payments pay Where p.ID = pay.ParentID
    AND pay.TheDate < @Date1
    ) as PrevMadf,

    (SELECT CurrMost + PrevMost) as AllMost,
    (SELECT CurrMadf + PrevMadf) as AllMadf,
    (SELECT AllMost - AllMadf) AS FinalTotal 

    from tbl_parents p    
) as tab
 WHERE FinalTotal <> 0

LukStorms · Answer 2 · 2021-12-29T18:12:07.007

0

It doesn't really make sense, since there's no GROUP BY

But change this

WHERE FinalTotal <> 0

To this

HAVING FinalTotal <> 0

Simplified test

create table test (
id int auto_increment primary key, 
a int, 
b int
);

insert into test (a, b) values (1,1),(2,3);

select a, b
, a + b as ab
, (select ab + 10) as ab10
from test
having ab10 > 12;

a	b	ab	ab10
2	3	5	15

db<>fiddle here

edited Dec 29 '21 at 18:12

answered Dec 29 '21 at 18:06

LukStorms

28,916
5
31
45

how to use Alias Name in where clause in SQL

2 Answers2