PostgreSQL Question posted in
The official documentation can be found here.

Postgresql – How to transform and summarize row counts

This is my data

with _temp_data as 
(

select 
    unnest(ARRAY['A','B','A','A']) as hobbies_1
    ,unnest(ARRAY['E','F','A','F']) as hobbies_2
)
select * from _temp_data

Input data:

hobbies_1|hobbies_2|
---------+---------+
A        |E        |
B        |F        |
A        |A        |
A        |F        |

I want to transform this table into single row combination like this

hobbies_1      |  hobbies_2
{'A':3,'B':1}  |{'E':1,'F':2,'F':1}
Tags:

2

Answers


  1. 0

    With the help of jsonb_object_agg you can do achive this using below query,

        WITH _temp_data AS (
    SELECT
        unnest(ARRAY['A','B','A','A']) AS hobbies_1,
        unnest(ARRAY['E','F','A','F']) AS hobbies_2
)
SELECT
    jsonb_object_agg(h1, count_h1) AS hobbies_1,
    jsonb_object_agg(h2, count_h2) AS hobbies_2
FROM (
    SELECT
        h1,
        COUNT(*) AS count_h1
    FROM (
        SELECT
            hobbies_1 AS h1
        FROM
            _temp_data
    ) AS subquery
    GROUP BY
        h1
) AS aggregated_hobbies_1
CROSS JOIN (
    SELECT
        h2,
        COUNT(*) AS count_h2
    FROM (
        SELECT
            hobbies_2 AS h2
        FROM
            _temp_data
    ) AS subquery
    GROUP BY
        h2
) AS aggregated_hobbies_2;

    The inner subqueries only select and group by the respective columns (hobbies_1 and hobbies_2) they are concerned with and then outer queries perform the aggregation and cross join

    Login or Signup to reply.
  2. 0

    You can do this in a single scan of the base table, by using GROUPING SETS and conditional aggregation.

    with _temp_data as 
(
    select 
      unnest(ARRAY['A','B','A','A']) as hobbies_1
     ,unnest(ARRAY['E','F','A','F']) as hobbies_2
),
Grouped AS (
    SELECT
      td.hobbies_1,
      td.hobbies_2,
      COUNT(*) AS count
    FROM _temp_data td
    GROUP BY GROUPING SETS (
      (td.hobbies_1),
      (td.hobbies_2)
    )
)
SELECT
  jsonb_object_agg(g.hobbies_1, g.count) FILTER (WHERE g.hobbies_1 IS NOT NULL) AS hobbies_1,
  jsonb_object_agg(g.hobbies_2, g.count) FILTER (WHERE g.hobbies_2 IS NOT NULL) AS hobbies_2
FROM Grouped g;

    db<>fiddle

    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search