Error in 'having' after 'group by ' clause

Question

I have the following sql query :

SELECT
     to_char(p.log_date, 'Mon') as mon,
     extract(year from p.log_date) as year,
from
     T
group by
     mon
having
     mon = 'september'
and
     year = '2018';

The table is in this format :

date        col1     col2
2018/09/10   --       --
2018/09/11   --       --
2018/09/12   --       --

T doesn't have the columns mon and year. I am getting the error ERROR: column "mon" does not exist. How to fix this?

Answer 1

use where clause no need having

SELECT
     to_char(p.log_date, 'Mon') as mon,
     extract(year from p.log_date) as year from your_table
where  to_char(p.log_date, 'Mon') = 'september'
and
     extract(year from p.log_date) = '2018'

As there no aggregate function so don't think here group by need

and mon is your column alis name which is not find db engine in filter so its thrown error.

Note: Not all dbms support alias name in filter(where or having)