Spark SQL how to use Filter-CodePudding

I have written the following SQL :

select  count(value) as total, name , window  from `event` 
   where count(value) > 1 group by window(event_time,'2 minutes'),name

Spark is giving me the following error :

nAggregate/Window/Generate expressions are not valid in where clause of the query.\nExpression in where clause: [(count(event.`value`) > CAST(1 AS BIGINT))]\nInvalid expressions: [count(event.`value`)]

What's the correct syntax ?

CodePudding user response：

You need to use HAVING instead (documentation), and it should be put after GROUP BY:

select  count(value) as total, name , window  from `event` 
group by window(event_time,'2 minutes'),name
having total > 1