Postgres: Get sum based on list in another column

Question

I have a table, locations like this:

location	sub_locations
A	A,a1,a2,a3
B	B,b1,b3

and another table, hours like this

location	hours
A	2
B	3
A	4
a1	1
a1	1
b3	8

for each location, I need to get the total hours for the sub_locations.

The result I'm looking for is this:

location	sub_locations	total_hours
A	A,a1,a2,a3	8
B	B,b1,b3	11

I can simply get the sum for the location with a simple join and group by location, but what I need is the total hours for all the sub locations. I've explored using another select statement for the total_hours column, but just got an empty result.

select 
l.location,
l.sub_locations,
sum(h.hours::float) as "sum plan hours for location"
from 
locations l 
join
hours h 
on l.location = h.location 
group by l.location,l.sub_locations

Storing comma separated values is a huge mistake to begin with. — user330315
– user330315, Commented Aug 10, 2022 at 13:46
Your Locations table doesn't even comply with first-normal form, fix this any everything becomes easier. — Stu
– Stu, Commented Aug 10, 2022 at 13:47
Thanks! I fixed the table to create a one to many relationship, and the query became a lot easier — dee
– dee, Commented Aug 15, 2022 at 9:44

user330315 · Accepted Answer · 2022-08-10 13:49:42Z

2

You can convert the dreaded comma separated string into an array and use that in a JOIN condition:

select l.location, l.sub_locations, sum(h.hours)
from locations  l 
 join hours h on h.location = any(string_to_array(l.sub_locations, ','))
group by l.location, l.sub_locations;

A better solution would be to fix your data model and create a proper one-to-many relationship that doesn't store locations/sub-locations in a CSV column.

answered Aug 10, 2022 at 13:49

user330315

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

1 Comment

dee Over a year ago

fixed table to one to many relationship instead. a simple group by query did the job after this

Rohit Bhati · Accepted Answer · 2022-08-10 13:54:02Z

0

I think think this will do the work for you, or if not tell me the result, so I can write again with more information.

  select  
    l.location,
    l.sub_locations,
    sum(h.hours::float) as "sum plan hours for location"
    from 
    locations l 
    join
    hours h 
    on l.location = h.location 
    WHERE h.location In (select sub_locations from locations where location = 'Your Location value(ex-A)')
    group by l.location,l.sub_locations

hope it helps

answered Aug 10, 2022 at 13:54

Rohit Bhati

5264 silver badges11 bronze badges

1 Comment

dee Over a year ago

Thanks Rohit. Didn't get around to try this, as decided to amend the locations table instead. This made the rest a lot simpler

Collectives™ on Stack Overflow

Postgres: Get sum based on list in another column

2 Answers 2

1 Comment

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related