Postgresql count from multiple columns in a row

Question

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?

Lots of ways to do this. One is proof_1::int + proof_2::int + proof_3::int. If you want something that can handle a variable number of proof_X columns, please say so as that can be done using to_jsonb() and jsonb_each() to pivot columns to rows. — Mike Organek
– Mike Organek, Commented May 25, 2022 at 11:23
Do you need the general case? I am procrastinating from some other stuff I need to do :-) so I put up an example: dbfiddle.uk/… — Mike Organek
– Mike Organek, Commented May 25, 2022 at 11:40

Kadet · Accepted Answer · 2022-05-25 13:27:45Z

1

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)

edited May 25, 2022 at 13:27

answered May 25, 2022 at 11:44

Kadet

1,4195 silver badges11 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Aasim Ansari · Accepted Answer · 2022-05-25 11:35:16Z

0

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

answered May 25, 2022 at 11:35

Aasim Ansari

1

1 Comment

barudo Over a year ago

this could be one way. But not elegant imho...

Anuj Chandra · Accepted Answer · 2022-05-25 11:41:41Z

0

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

answered May 25, 2022 at 11:41

Anuj Chandra

111 bronze badge

Collectives™ on Stack Overflow

Postgresql count from multiple columns in a row

3 Answers 3

Comments

1 Comment

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

Comments

1 Comment

Comments

Your Answer

Sign up or log in

Post as a guest