Struggling with simple SQL query

Question

I have two tables in my database, one table holds the registered users and other table for referrals.

Below is the referrals table

enter image description here

What I want to do is to make an SQL query that returns the referral name "rname" and all users referred by this referral (total users and premium users)

In the users table, I have a column named "package" of type INT, if the value of this column is bigger than 0 then the user is premium, otherwise, the user isn't premium.

The "ruser_referred" column in the image above represents the id of the user being referred.

So the result should look like this (supposing only user with id 21 is premium)

Referral       Total Users Referred          Premium Users Referred
rafik              3                                        1
samia           1                                         0
mourad        1                                         0

Possible duplicate of: http://stackoverflow.com/questions/12475850/how-can-an-sql-query-return-data-from-multiple-tables — Fluffeh, Sep 01 '14 at 20:33

score 3 · Accepted Answer · answered Sep 01 '14 at 20:35

3

This is a join with conditional aggregation:

select r.name, count(*) as NumReferrals,
       sum(case when u.package > 0 then 1 else 0 end) as NumPremium
from referrals r join
     users u
     on r.ruser_referred = u.userid
group by r.name;

answered Sep 01 '14 at 20:35

Gordon Linoff

1,242,037
58
646
786

What a great answer. Thank you very much! You're a day saver – Rafik Bari Sep 01 '14 at 20:55

Struggling with simple SQL query

1 Answers1