Annas Rafique Annas Rafique - 1 month ago 14
SQL Question

How to count number of Cases in SQL?

How can one find number of Cases in SQL? For example, following code has 5 Case statements

Select
up.user_id,
(
case when up.email is null then 0 else 1 end
+
case when up.bio is null then 0 else 1 end
+
case when up.website is null then 0 else 1 end
+
case when up.location is null then 0 else 1 end
+
case when up.name is null then 0 else 1 end
) * 100 / **5** as complete
from users_profiles up


I am using PostgreSQL

Answer

Use a function with variable number of arguments, e.g.:

create or replace function not_null_ratio(variadic args text[])
returns numeric language plpgsql as $$
declare
    res numeric = 0;
begin
    for i in 1..array_length(args, 1) 
    loop
        res := res+ (args[i] is not null)::integer;
    end loop;
    return res * 100 / array_length(args, 1);
end $$;

select not_null_ratio('a', 'b', null, 'd');

   not_null_ratio    
---------------------
 75.0000000000000000
(1 row)

In your query:

select
    up.user_id,
    not_null_ratio(up.email, up.bio, up.website, up.location, up.name) as complete
from users_profiles up

SQL functions can also have variable number of arguments. This variant may be a bit faster:

create or replace function not_null_ratio_sql(variadic args text[])
returns numeric language sql as $$
    select count(elem) * 100.0 / count(*)
    from unnest(args) elem
$$;

select not_null_ratio_sql('a', 'b', null);

 not_null_ratio_sql  
---------------------
 66.6666666666666667
(1 row)
Comments