Unknown column in Having Clause

Question

Getting this apparently nonsensical error

Error code: 1054 Unknown column in having Clause

while running this query

Select date(START_TS), resource_key, count(INTERACTION_RESOURCE_ID) 
From tableName
Group By date(START_TS), resource_key 
Having date(START_TS) > '2020-08-27' and resource_key in(772122, 772134);

Although the objective has been achieved by using Where Clause before Group By, but this error is still bothering. Any help would be appreciated.

score 1 · Accepted Answer · answered Sep 11 '20 at 18:40

Havin can only use colmuns that are in the GROUP By or SELECT. But START_TS as columns doesn't exist, so you must use an ALIAS to get your dates.

Schema (MySQL v5.7)

CREATE TABLE tableName (
  `START_TS` date,
  `resource_key` int,
  `INTERACTION_RESOURCE_ID` varchar(4)
);
INSERT INTO tableName
  (START_TS, resource_key, INTERACTION_RESOURCE_ID)
VALUES
  ('2020-08-27' , 772122, 'test'),
    ('2020-08-27' , 772122, 'test')
;

Query #1

Select date(START_TS) date_ts, MIN(resource_key), count(INTERACTION_RESOURCE_ID) 
From tableName
Group By date(START_TS), resource_key 
Having date_ts >= '2020-08-27' and resource_key in (772122, 772134);



  date_ts
  MIN(resource_key)
  count(INTERACTION_RESOURCE_ID)




  2020-08-27
  772122
  2

View on DB Fiddle

Unknown column in Having Clause

1 Answers1