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

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?

CodePudding user response：

You can not use the alias "cnt" in the HAVING clause in this query :

select "referral_code", count(*) as "cnt" from "users" group by "users"."referral_code" having "cnt" > $1

because the "cnt" alias is not been created when SQL engine processing the HAVING clause, for more details read this

CodePudding user response：

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