Adding a filter for MIN function using WHERE condition Error Code: 1111. Invalid use of group functi-CodePudding

I need have below query which returns the MIN(date) from the table, however I want to add a where condition to the query to select the dates only between 2021-01-01 and 2021-04-01.

SELECT MIN(file.date_received) as date
FROM `file`
JOIN `ref` ON `ref`.`r_id` = `file`.`r_id`
JOIN `screening` ON `screening`.`screen_id` = `ref`.`r_id`
WHERE `ref`.`out` = 4
GROUP BY `file`.`r_id

When I add the below where condition to the query, I get the error below

Error Code: 1111. Invalid use of group function

SELECT MIN(file.date_received) as date
FROM `file`
JOIN `ref` ON `ref`.`r_id` = `file`.`r_id`
JOIN `screening` ON `screening`.`screen_id` = `ref`.`r_id`
WHERE `ref`.`out` = 4
AND  MIN(file.date_received)  > '2021-01-01' AND  MIN(file.date_received)  < '2021-04-01'
GROUP BY `file`.`r_id

CodePudding user response：

for aggregated result you need HAVING

SELECT MIN(file.date_received) as date
FROM `file`
JOIN `ref` ON `ref`.`r_id` = `file`.`r_id`
JOIN `screening` ON `screening`.`screen_id` = `ref`.`r_id`
WHERE `ref`.`out` = 4
GROUP BY `file`.`r_id
HAVING MIN(file.date_received)  > '2021-01-01'  AND MIN(file.date_received)  < '2021-04-01

CodePudding user response：

It is unnecessary to use MIN in WHERE-clause since you are filtering your results in your SELECT-statement.

To range a date you could use BETWEEN instead.

SELECT MIN(file.date_received) as date
FROM `file`
JOIN `ref` ON `ref`.`r_id` = `file`.`r_id`
JOIN `screening` ON `screening`.`screen_id` = `ref`.`r_id`
WHERE `ref`.`out` = 4
AND  file.date_received BETWEEN CAST('2021-01-01' AS DATE) AND CAST('2021-04-01' AS DATE) 
GROUP BY `file`.`r_id