CODEWITHSUNDEEP
sqlpostgresqlgroup-bywhere-clausehaving
Jarvis
Jarvis

Reputation: 8564

Error in 'having' after 'group by ' clause

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?

Upvotes: 0

Views: 222

Answers (5)

Abuzar Ansari
Abuzar Ansari

Reputation: 63

This is very simple, Try this. 

Select mon, [year] from (
  SELECT
     to_char(p.log_date, 'Mon') as mon,
     extract(year from p.log_date) as year,
 from T

) as DerivedTable

 where  mon = 'september' and  year = '2018'

group by mon

Upvotes: 0

Zaynul Abadin Tuhin
Zaynul Abadin Tuhin

Reputation: 31991

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)

Upvotes: 3

Fahmi
Fahmi

Reputation: 37483

Try below - this occured as you use alias name 

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

Upvotes: 1

D-Shih
D-Shih

Reputation: 46249

Having used on aggregate function condition, but you don't need to use it because to_char and extract are normal function.

I think you forget to use FROM clause to select your table.

SELECT
     to_char(p.log_date, 'Mon') as mon,
     extract(year from p.log_date) as year,
FROM T
WHERE 
    to_char(p.log_date, 'Mon') = 'september'
AND
    extract(year from p.log_date) = '2018';

Upvotes: 1

S-Man
S-Man

Reputation: 23756

Using an alias in the HAVING clause causes the problem. You could use the to_char expression in the HAVING (or WHERE clause). But to avoid code replication you should do a subquery or a CTE:

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

CTE: 

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

Furthermore:

  1. Try to use WHERE instead of HAVING
  2. To group by month and year you could do date_trunc('month', p.log_date) instead of to_char and extract

Upvotes: 1

Related Questions