Matthieu Matthieu - 2 months ago 8
SQL Question

MySQL: percentage of filled field

I have the following MySQL table "user".

Name Phone City
----------------------------
Mike | 154 | London
NULL | 659 | Paris
Matt | NULL | NULL
NULL | NULL | NULL
Marc | 514 | NULL


Expected Result:

COUNT of 3/3 not NULL | COUNT of 2/3 not NULL | COUNT of 1/3 not NULL
----------------------------------------------------------------------
1 | 2 |1


I would like to know the number of users who completed his profile 100%, 66%, 33%, 0%

What query can give me this result ?

Thanks a lot !

Answer

You can use conditional aggregation for this:

Select  Sum(Case When TotalNotNull = 3 Then 1 Else 0 End) As `Count of 3/3 not NULL`,
        Sum(Case When TotalNotNull = 2 Then 1 Else 0 End) As `Count of 2/3 not NULL`,
        Sum(Case When TotalNotNull = 1 Then 1 Else 0 End) As `Count of 1/3 not NULL`,
        Sum(Case When TotalNotNull = 0 Then 1 Else 0 End) As `Count of 0/3 not NULL`
From
(
    Select  Case When Name  Is Not Null Then 1 Else 0 End 
        +   Case When Phone Is Not Null Then 1 Else 0 End
        +   Case When City  Is Not Null Then 1 Else 0 End
            As TotalNotNull
    From    `User`
) As A