Postgresql count from multiple columns in a row-CodePudding

I have this table on a postgres db (proofs). proof_1, proof_2 and proof_3 are boolean columns indicating whether the user (user_id) has submitted the proofs:

user_id	proof_1	proof_2	proof_3
1	true	true	false
2	true	false	false
3	true	true	true

I need to count how many proofs are submitted by each user. This is the query that I came up:


> select 
    user_id,
    length(
      concat(
        case when proof_1 then '1' end,
        case when proof_2 then '1' end,
        case when proof_3 then '1' end)
      )) as proof_counts
  from
    proofs

The query above would work. But I don't think that it is the best query to do. Please advice on what query should be done?

CodePudding user response：

select user_id, 
      case when proof_1 then 1 else 0 end 
      case when proof_2 then 1 else 0 end        
      case when proof_3 then 1 else 0 end  as proof_counts   
 from (
    values (1,true,true,false), (2,false,true,false)
  ) as proof (user_id, proof_1, proof_2, proof_3)

If there is no null values then below query

select user_id, 
      proof_1::integer 
      proof_2::integer 
      proof_3::integer   as proof_counts   
 from (
    values (1,true,true,false), (2,false,true,false)
  ) as proof (user_id, proof_1, proof_2, proof_3)

And version which handle nulls

select user_id, 
      coalesce(proof_1::integer,0) 
      coalesce(proof_2::integer,0) 
      coalesce(proof_3::integer,0)   as proof_counts   
 from (
    values (1,null,true,false), (2,false,true,false)
  ) as proof (user_id, proof_1, proof_2, proof_3)

CodePudding user response：

I think this is also a good alternative:

select user_id, 
       sum(case when proof_1 = true then 1 else 0 end)   sum(case when proof_2  = true then 1 else 0 end)   sum(case when proof_3 = true then 1 else 0 end) as proof_counts
    from proofs
    group by user_id

CodePudding user response：

This is possible just by converting your boolean to integer and then summing the three columns

Query:

select
userid_id,
sum(case when proof_1=true then 1 end) sum(case when proof_2=true then 1 end) sum(case when proof_2=true then 1 end) as total_proofs_submitted
from proofs
group by user_id