MySQL show proportion

I am trying to show a proportion of customers that signed up because he/she was being referred by other customers and customers with no referral. So far I only able to show it as numerical but I wanted to show it in percentage. Null is when the customer signs up without being referred.

The original data as follows:

CustomerID    ReferralID
1000004        1000003
1000015        1000010
1000007        1000004
1000011           Null
1000026        1000004

The query that I have and return data as follows:

select customerID, COUNT(*) as proportion
from company123.customertable
group by (customerID)
order by customerID asc;

CustomerID    proportion        
1000004         1               
1000015         1               
1000007         1              
1000011         1               
1000026         1

Expected result

CustomerID    referred        non-referred
1000004         1                  0
1000015         1                  0
1000007         1                  0
1000011         0                  1
1000026         1                  0

Any suggestion to show it as a percentage? Thank you in advance

Answer

Use aggregate function AVG():

select avg(ReferralID is not null) referred,
       avg(ReferralID is null) non_referred 
from customertable

See the demo.
Results:

> referred | non_referred
> -------: | -----------:
>   0.8000 |       0.2000

Advertisement

Answer