I have a table, locations like this:

and another table, hours like this

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

The result I'm looking for is this:

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

CodePudding user response：

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.

CodePudding user response：

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