CODEWITHSUNDEEP
sqlpostgresql
Black
Black

Reputation: 5367

sql to sum rows of booleans into rows

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)

Upvotes: 0

Views: 128

Answers (2)

TPAKTOPA
TPAKTOPA

Reputation: 2682

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

Upvotes: 2

Black
Black

Reputation: 5367

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

Upvotes: 0

Related Questions