I'm trying to sum salaries for workers and have this sum in the corresponding company record. Company and workers are connected through "code" and "parent company".

Expected output:

How can I put sum of salaries in company row if company and workers are connected through different columns?

Thanks for help in advance.

CodePudding user response：

you can join a subquery with the sum

SELECT 
  tab1."Description", tab1."level"
  ,  tab1."salary" 
  , tab1."code", tab1."parent company"
  ,tab2."salary" company_salary
  FROM table1 tab1 LEFT JOIN 
  (SELECT
SUM("salary") salary, "parent company" 
  FROM table1 
  WHERE "parent company" IS NOT NULL
  GROUP By "parent company" ) tab2 ON tab1."code" = tab2."parent company" 

SELECT 3

CodePudding user response：

You can do it with a window function, conditionally with respect to the description.

SELECT *, 
       CASE WHEN Description = 'Company name'
            THEN SUM(salary) OVER(PARTITION BY COALESCE(parent_company, code))
       END AS company_salary
FROM tab

Check the demo here.

Note: This works even if you have multiple different companies.