Cannot use window function 'count' in having statement

Question

I'm new to MYSQL, and I'm trying to validate the number of data which have the same name from 2 column that occurs more than one time, which I already try to use 'having' statement in this case and it throws me an error like this Error Code: 3593. You cannot use the window function 'count' in this context.' below I include an image of what I'm trying to do

you can see there a column named "number_of_same_year" represent the "COUNT OVER PARTITION" output, which has numbers that logically could be validated. I only want to show where the numbers are above 1 (which means occur more than one time)

ps: I'm using MySQL in Windows 10

I cannot use the "where" statement either. so it seems like there's no way to validate it — greendino, Mar 04 '20 at 17:13
I believe WINDOW functions are only allowed in SELECT/ORDER clause. — donut, Mar 04 '20 at 17:23

score 3 · Accepted Answer · answered Mar 04 '20 at 17:14

You cannot use having and a window function. You would want to instead do as follows

select * from (
select unit_name
       ,month(transaction_date)
       ,year(transaction_date) as year
       ,budget
       ,count(*) over(partition by unit_name,year(transaction_date)) as number_of_same_year
  from sql_advertising.history_transaction
  )x
where x.number_of_same_year >1
order by x.unit_name

score 1 · Answer 2 · answered Mar 04 '20 at 17:19

SELECT {fieldset}
FROM {tableset}
WHERE {conditions-1}
GROUP BY {expression-1}
HAVING {conditions-2}
   AND {expression-2} = COUNT({expression-3}) OVER ({window})

Window function is applied to output dataset, but HAVING alters it. So window function cannot be used in HAVING. The above code is invalid.

You may solve it by:

WITH `cte` AS ( SELECT {fieldset}, 
                       {expression-2} = COUNT({expression-3}) OVER ({window}) AS `criteria`
                FROM {tableset}
                WHERE {conditions-1}
                GROUP BY {expression-1}
                HAVING {conditions-2} )
SELECT {fieldset}
FROM `cte`
WHERE `criteria`

Cannot use window function 'count' in having statement

2 Answers2