CODEWITHSUNDEEP
sqldatabaseoracle-databaseoracle11g
OT AMD
OT AMD

Reputation: 183

how to count number of clients referred by client

Hello everyone i seem not to solve my problem which is i need to list client id along with the count of numbers of clients referred by the client... heres my table as follow:

clientID    Name    Referred
10001       jhon     null
10002       scott    10001
10003       james    null
10004       nelson   10003
10005       chris    10003
10006       jack     10001
10007       jeremy   null
10008       cliford  10002
10009       matt     null
10010       justin   10004
10011       brad     10001
10012       jason    10008

Upvotes: 0

Views: 119

Answers (2)

Gordon Linoff
Gordon Linoff

Reputation: 1270793

If you just need the clientId for anyone with a referral, this is a simple group by:

select referred as clientId, count(*) as cnt
from t
where referred is not null
group by referred;

Upvotes: 1

collapsar
collapsar

Reputation: 17258

Aggregate the data by the 'referred' column and join the result with the complete table (assuming 'clientdata' to be the table name):

   Select c.clientID
        , nvl(r.cnt, 0)
     From clientdata c
Left join (
              Select referred
                   , count(*) cnt
                From clientdata
            Group by referred
          ) r
       On (r.referred = c.clientid)
        ;

Upvotes: 0

Related Questions