sql to sum rows of booleans into rows

Question

I'm trying to work out if it's possible to do the following transformation in SQL:

+--------+--------+--------+
|POLICY_1|POLICY_2|POLICY_3|
+--------+--------+--------+
|T       |T       |F       |
+--------+--------+--------+
|F       |T       |F       |
+--------+--------+--------+
|T       |T       |T       |
+--------+--------+--------+

Is it possible to query this table and end up with a result set that looks like:

+------+-----+
|POLICY|COUNT|
+------+-----+
|1     |2    |
+------+-----+
|2     |3    |
+------+-----+
|3     |1    |
+------+-----+

I'm wondering in general SQL terms, but incase it matters I'm using Postgres (9.2)

Make a pivot for this

Amit Bisht
– Amit Bisht

2014-01-15 06:51:21 +00:00
Commented Jan 15, 2014 at 6:51 — Amit Bisht
– Amit Bisht, Commented Jan 15, 2014 at 6:51

TPAKTOPA · Accepted Answer · 2014-01-22 14:51:36Z

2

Union All, Aggregate and CASE Version

 select 1 as POLICY, SUM(case when POLICY_1 = 'T' THEN 1 ELSE 0 end) as COUNT
  from POLICIES
  union all
 select 2 as POLICY, SUM(case when POLICY_2 = 'T' THEN 1 ELSE 0 end) as COUNT
  from POLICIES
  union all
 select 3 as POLICY, SUM(case when POLICY_3 = 'T' THEN 1 ELSE 0 end) as COUNT
  from POLICIES

*Unpivot Version: *(MicroSoft T-SQL specific)

If you insist, to convert a row to columns, you can use PIVOT/UNPIVOT functionality.

SELECT ROW_NUMBER() OVER (ORDER BY PolicyName) AS Row, * 
 FROM ( select SUM(CASE WHEN Policy_1 = 'T' THEN 1 ELSE 0 END) as Policy_1,
               SUM(CASE WHEN Policy_2 = 'T' THEN 1 ELSE 0 END) as Policy_2,
               SUM(CASE WHEN Policy_3 = 'T' THEN 1 ELSE 0 END) as Policy_3
        from POLICIES
      )p
 UNPIVOT ( T_Count FOR PolicyName  in ([Policy_1], [Policy_2], [Policy_3]))unpvt

Unnest version ( postgre sql specific )
All credits go to Francis, topic caster, i just post it here.

select
    UNNEST((select array_agg(generate_series) 
            from generate_series(1,3))) as policy_name,
    UNNEST(array[
             sum(case when policy_1 = 't' then 1 else 0 end),
             sum(case when policy_2 = 't' then 1 else 0 end),
             sum(case when policy_3 = 't' then 1 else 0 end)
           ]) as count from POLICY

edited Jan 22, 2014 at 14:51

answered Jan 15, 2014 at 6:53

TPAKTOPA

2,7522 gold badges21 silver badges27 bronze badges

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

5 Comments

Black Over a year ago

the first part of your answer is a solution, but to be honest I was hoping there was another way. Ideally, the query wouldn't have to change based on how many columns there were. I'm going to leave the question open for a bit incase there are other suggestions.

TPAKTOPA Over a year ago

The other way is PIVOT/UNPIVOT, but it's not ANSI SQL compatible. Fortunally for you, postgresql supports PIVOT and Row_Number. I am not familiar with postgresql, this example is working with MicroSoft T-SQL. You may need some tweaking to adapt it to postgre, though.

Black Over a year ago

I've used UNNEST for this in Postgres, it's at least 3x quicker than the UNION ALL approach in my case

TPAKTOPA Over a year ago

Would be good, if u add your solution, so ppl who google for your question, see your solution too. MS and FireBird have no UNNEST , so i can't generate example

user330315 Over a year ago

@TPAKTOPA: since when does Postgres support PIVOT or UNPIVOT ? (Btw the names are Microsoft or Firebird, not MicroSoft or FireBird)

Black · Accepted Answer · 2014-01-20 21:53:52Z

0

As suggested, here is the UNNEST version for Postgresql:

select
    UNNEST((select array_agg(generate_series) from generate_series(1,3))) as policy_name,
    UNNEST(array[
        sum(case when policy_1 = 't' then 1 else 0 end),
        sum(case when policy_2 = 't' then 1 else 0 end),
        sum(case when policy_3 = 't' then 1 else 0 end)
    ]) as count from POLICY

answered Jan 20, 2014 at 21:53

Black

5,35710 gold badges72 silver badges100 bronze badges

Collectives™ on Stack Overflow

sql to sum rows of booleans into rows

2 Answers 2

5 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

5 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related