CODEWITHSUNDEEP
mysqlsql
sqlnewbie
sqlnewbie

Reputation: 29

sql to show top customer to gave most referral

I have customerID and referralID data. referralID refers to another customer ID that gave referral for a particular customer (e.g. customer 1004 sign up because was referred by customer 1003, and in turn customer 1004 gave referral for customer 1007 and 1026). Customer 1011 sign up without being referred by anyone.

customerID         referralID
1004               1003
1015               1010
1007               1004
1011               null
1026               1004

The following is my query and I want to show the top 10 customer that gave most referral:

select customerID, count(referralID) as mostReferral
from company123.customertable 
group by customerID
order by count(referralID) DESC
limit 10;

The result that I got is:

customerID         mostReferral
1004               1
1015               1
1007               1
1011               1
1026               1

And customer 1004 should give 2 referral id, is it not? Any suggestions how to only show the top 10 customer that gave most referral?

Upvotes: 0

Views: 407

Answers (1)

Sahar Rezazadeh
Sahar Rezazadeh

Reputation: 333

use this:

select referralID , COUNT(*) as ReferralCount
from company123.customertable
group by (referralID)
order by referralID DESC
limit 10;

Upvotes: 1

Related Questions