Search code examples
sqlnode.jsknex.js

how to write this SQL query with group by in knex?

How to write this query? SELECT referral_code, count(*) FROM users GROUP BY users.referral_code HAVING count(*) > 1

I spent too much time today on it without any success always getting:

Error: error: select "referral_code", count(*) as "cnt" from "users" group by "users"."referral_code" having "cnt" > $1 - column "cnt" does not exist

Surely there must be a way?

Solution

  • do you want to use Knex or SQL statment? in Knex you should use

    knex('users')
.groupBy('referral_code')     
.having('count', '>', 1)

    but in sql you have to use Field name in having. cnt should be delete in the having