Grouping by similar values in multiple columns

Question

I have a table of entities with an id, and a category (few different values with NULL allowed) from 3 different years (category can be different from 1 year to another), in 'wide' table format:

| ID  | CATEG_Y1 | CATEG_Y2 | CATEG_Y3 |
+-----+----------+----------+----------+
| 1   | NULL     | B        | C        |
| 2   | A        | A        | C        |
| 3   | B        | A        | NULL     |
| 4   | A        | C        | B        |
| ... | ...      | ...      | ...      |

I would like to simply count the number of entities by category, grouped by category, independently for the year:

+-------+----+----+----+
| CATEG | Y1 | Y2 | Y3 |
+-------+----+----+----+
| A     | 6  | 4  |  5 | <- 6 entities w/ categ_y1, 4 w/ categ_y2, 5 w/ categ_y3  
| B     | 3  | 1  | 10 |
| C     | 8  | 4  |  5 |
| NULL  | 3  | 3  |  3 |
+-------+----+----+----+

I guess I could do it by grouping values one column after the other and UNION ALL the results, but I was wondering if there was a more rapid & convenient way, and if it can be generalized if I have more columns/years to manage (e.g. 20-30 different values)

score 1 · Answer 1 · answered Jun 14 '17 at 22:18

A bit clumsy, but probably someone has a better idea. Query first collects all diferent categories (the union-query in the from part), and then counts the occurences with dedicated subqueries in the select part. One could omit the union-part if there is a table already defining the available categories (I suppose categ_y1 is a foreign key to such a primary category table). Hope there are not to many typos:

select categories.cat,
       (select count(categ_y1) from table ty1 where select categories.cat = categ_y1) as y1,
       (select count(categ_y2) from table ty2 where select categories.cat = categ_y2) as y2,
       (select count(categ_y3) from table ty3 where select categories.cat = categ_y3) as y3
from ( select categ_y1 as cat from table t1
 union select categ_y2 as cat from table t2
 union select categ_y3 as cat from table t3) categories

OK similar to @Gordon Linoff proposition – ant1j Jun 15 '17 at 09:48 — ant1j, Jun 15 '17 at 09:48

score 1 · Answer 2 · answered Jun 14 '17 at 22:46

Use jsonb functions to transpose the data (from the question) to this format:

select categ, jsonb_object_agg(key, count) as jdata
from (
    select value as categ, key, count(*)
    from my_table t,
    jsonb_each_text(to_jsonb(t)- 'id')
    group by 1, 2
    ) s
group by 1
order by 1;

 categ |                     jdata                     
-------+-----------------------------------------------
 A     | {"categ_y1": 2, "categ_y2": 2}
 B     | {"categ_y1": 1, "categ_y2": 1, "categ_y3": 1}
 C     | {"categ_y2": 1, "categ_y3": 2}
       | {"categ_y1": 1, "categ_y3": 1}
(4 rows)

For a known (static) number of years you can easily unpack the jsonb column:

select categ, jdata->'categ_y1' as y1, jdata->'categ_y2' as y2, jdata->'categ_y3' as y3 
from (
    select categ, jsonb_object_agg(key, count) as jdata
    from (
        select value as categ, key, count(*)
        from my_table t,
        jsonb_each_text(to_jsonb(t)- 'id')
        group by 1, 2
        ) s
    group by 1
    ) s
order by 1;

 categ | y1 | y2 | y3 
-------+----+----+----
 A     | 2  | 2  | 
 B     | 1  | 1  | 1
 C     |    | 1  | 2
       | 1  |    | 1
(4 rows)

To get fully dynamic solution you can use the function create_jsonb_flat_view() described in Flatten aggregated key/value pairs from a JSONB field.

I like the `json` approach. I learned that using `jsonb_each_text` can be very helpful to "melt" a table (from "wide" to "long" format); very convenient in many cases. Benchmarking your approach unfortunately gives slower results compared to a `UNION ALL` query. — ant1j, Jun 15 '17 at 09:52
Yes, simple union is more efficient as json conversions are costly. However you've asked about dynamic solutions. With the cited function you're able to get a fully automated pivot table. — klin, Jun 15 '17 at 10:21

score 1 · Answer 3 · answered Jun 14 '17 at 22:48

I would do this as using union all followed by aggregation:

select categ, sum(categ_y1) as y1, sum(categ_y2) as y2,
       sum(categ_y3) as y3
from ((select categ_y1, 1 as categ_y1, 0 as categ_y2, 0 as categ_y3
       from t
      ) union all
      (select categ_y2, 0 as categ_y1, 1 as categ_y2, 0 as categ_y3
       from t
      ) union all
      (select categ_y3, 0 as categ_y1, 0 as categ_y2, 1 as categ_y3
       from t
      )
     )
group by categ ;

Yes that the more direct approach – ant1j Jun 15 '17 at 09:48 — ant1j, Jun 15 '17 at 09:48

Grouping by similar values in multiple columns

3 Answers3