KsqlDB query pull with group by clauses

need an advice or some ideas for my case.

here is my query pull

{ “ksql”: “SELECT rtdate as trans_date , trb102 as account_number, count() as freq_trans_today FROM trx_log WHERE rtdate = ‘2022-12-29’ GROUP BY rtdate , trb102 having count() >= 3;”, “streamsProperties”: {} }

but i got an error

{ “@type”: “statement_error”, “error_code”: 40001, “message”: “Pull queries don’t support GROUP BY clauses. See Queries - ksqlDB Documentation for more info.\nAdd EMIT CHANGES if you intended to issue a push query.\nStatement: SELECT rtdate as trans_date , trb102 as account_number, count() as freq_trans_today FROM trx_log WHERE rtdate = ‘2022-12-29’ GROUP BY rtdate , trb102 having count() >= 3;”, “entities”: }

when i add EMIT CHANGES , the request being LISTEN not return the value. Please Help

Hmm, I checked the ksqlDB docs:

Pull queries do not support JOIN, PARTITION BY, GROUP BY and WINDOW clauses (but can query materialized tables that contain those clauses).

To get around this, you might try creating a table first and then issuing a pull query against the table. Let me know if that works!