How to use Group by and Having in a SQL query in NiFi ?

[#1]

Hi All,

I have been trying to using group by with having clause in the ExecuteSQL processor but it doesn't seem to be returning the count.

The SQL query looks like :

select id_num, count(*) from xyz 

where id_num<>0

group by id_num

having count(*)>1

I put this in the SQL select query. I just get an empty array. How do I use group by and having in the select query in Nifi? A normal query without these clauses works perfectly but I need a count to check for duplicates in the database.

Thank you in Advance

[#2 · Accepted solution]

I just realised that NiFi only supports "Group by" and doesn't support "HAVING" clause. I had to put the queries in a python script and call an execute stream command processor.