Search code examples
mysqlsqlcountmysql-workbenchhaving-clause

Cannot use window function 'count' in having statement

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 mysql workbench screenshot

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

Solution

  • 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