Calculate percentage of used fields(columns) in MySQL

Question

Hi there,

I would like to know if there is any query to calculate the count of used fields(columns) in a table for every row(record).

I want to update my table new field called percentage usage by calculating

(total number of used columns) / (total number columns) * 100

for all records.

Any suggestion is appreciated. Thanks

---

For example:

I have a table named leads:

Name     Age      Designation    Address

Jack      25      programmer     chennai   
Ram       30      -----------    ----------                   
Rob       35      Analyst        ----------                     

I have added a new column called usagepercent and I want to update the new field as

Name     Age      Designation    Address     usagepercent

Jack      25      programmer     chennai      100
Ram       30      -----------    ----------    50
Rob       35      Analyst        ----------    75

------- indicates empty

Answer 1

Something like this should work (if the default/empty/unused value of the fields is Null):

SET @percValue=25;
UPDATE
    leads
SET
    usagePercent =
        IF(Name IS NOT NULL, @percValue, 0) +
        IF(Age IS NOT NULL, @percValue, 0) +
        IF(Designation IS NOT NULL, @percValue, 0) + 
        IF(Address IS NOT NULL, @percValue, 0);

You'll have to change percValue according to the number of columns you have.

Edit: Adapted solution of RSGanesh:

UPDATE
    leads
SET
    usagePercent = (
        IF(Name IS NOT NULL, 1, 0) +
        IF(Age IS NOT NULL, 1, 0) +
        IF(Designation IS NOT NULL, 1, 0) + 
        IF(Address IS NOT NULL, 1, 0)
        ) / 4 * 100;