CODEWITHSUNDEEP
sqlnode.jsknex.js
Capaj
Capaj

Reputation: 4206

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?

Upvotes: 0

Views: 908

Answers (2)

Shahed Mahmoudi
Shahed Mahmoudi

Reputation: 281

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

Upvotes: 2

Mohamad Jamil
Mohamad Jamil

Reputation: 29

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

Upvotes: 1

Related Questions