Postres SQL select with GROUP BY and COUNT

i have this SQL select

SELECT t.debtor_account, COUNT(t.debtor_account)
FROM transaction t
GROUP BY t.debtor_account
HAVING COUNT(t.debtor_account) > 2;

which work, but i need select all from transaction. But when i try SELECT * FROM …… i get this error: column “t.account_fk” must appear in the GROUP BY clause or be used in an aggregate function Any idea for select with same functionality, but with select all instead of just debtor_account ? Thanks…

Btw. Table scheme here

Answer

You can use window functions:

SELECT t.*
FROM (SELECT t.*, COUNT(*) OVER (PARTITION BY t.debtor_account) as cnt
      FROM transaction t
     ) t
WHERE cnt > 2
ORDER BY debtor_account;

Advertisement

Answer