CODEWITHSUNDEEP
sqlpostgresqljoin
sirjay
sirjay

Reputation: 1766

SQL how to get select attribute that is array by join?

I am using postgresql. How to get from first table to second? Thank you

 id  | type | sum         
-----+------+-----
 1   | a    | 100
 2   | a    | 200
 3   | b    | 500


 t_sum | type | history          
-------+------+---------------
 300   | a    | ['id' => 1, 'sum' => 100], ['id' => 2, 'sum' => 200]
 500   | b    | ['id' => 3, 'sum' => 500]

I tried this, no results:

SELECT DISTINCT(a.type), SUM(a.sum) as t_sum, b.* as history FROM mytable a LEFT JOIN mytable b ON a.id = b.id GROUP BY a.type

Upvotes: 2

Views: 170

Answers (1)

Laurenz Albe
Laurenz Albe

Reputation: 247830

The following solution returns the last column as a json array:

SELECT sum(sum) AS t_sum,
       type,
       array_to_json(
          array_agg(
             json_build_object('id', id, 'sum', sum)
          )
       ) history
FROM history
GROUP BY type
ORDER BY type;

┌───────┬──────┬───────────────────────────────────────────────────┐
│ t_sum │ type │                      history                      │
├───────┼──────┼───────────────────────────────────────────────────┤
│   300 │ a    │ [{"id" : 1, "sum" : 100},{"id" : 2, "sum" : 200}] │
│   500 │ b    │ [{"id" : 3, "sum" : 500}]                         │
└───────┴──────┴───────────────────────────────────────────────────┘
(2 rows)

Upvotes: 3

Related Questions