Query to separate, then count a field of comma separated values in postgresql

Question

I have a table in postgresql that starts like this:

car_id  part_ids     total_inventory
------  --------     ----------
10134   101,506,589  50
12236   201,506,101  20
78865   201,399,304  10

I'm trying to write a query and/or view that will separate each of the part_ids on the comma, count the sum total_inventory together for each part_id, and then include all of the part_ids in a single column like this:

part_ids total_inventory
-------- ----------
101      70
506      70   
589      50
201      30
399      10
304      10

I've tried using unnest(string_to_array) on the part_ids column to get the end result - but haven't had too much luck.

Anyone have any ideas? Thanks for helping!

P.S. this is my first question - any recommendations/edits please let me know

You shouldn't be storing comma separated values in the first place — user330315
– user330315, Commented Jun 7, 2018 at 21:15

user330315 · Accepted Answer · 2018-06-07 21:17:06Z

4

Something like this should work

select p.part_id, 
       sum(t.total_inventory) as total_inventory
from parts t
  cross join lateral unnest(string_to_array(part_ids, ',')::int[]) as p(part_id)
group by p.part_id

Online example: http://rextester.com/NVTCG56767

answered Jun 7, 2018 at 21:17

user330315

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

Comments

Diego Victor de Jesus · Accepted Answer · 2018-06-07 21:26:53Z

0

Try this:

WITH X AS 
(
    SELECT car_id, UNNEST(REGEXP_SPLIT_TO_ARRAY(part_ids, ',')) AS part_ids, total_inventory FROM parts
)
SELECT part_ids, SUM(total_inventory) AS total_inventory FROM X GROUP BY part_ids
ORDER BY total_inventory DESC;

In the first step i create a temporary table to hold unnested values.

Then i group part_ids and sum their related total_inventory.

answered Jun 7, 2018 at 21:26

Diego Victor de Jesus

3,0812 gold badges23 silver badges34 bronze badges

Collectives™ on Stack Overflow

Query to separate, then count a field of comma separated values in postgresql

2 Answers 2

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related