I will try my best to explain this. Let’s say I have a table like this
| Name | Year | Present_category | Present_count | Mother |
|---|---|---|---|---|
| Mary | 2023 | sweets | 3 | Linda |
| Mary | 2022 | sweets | 2 | Linda |
| Linda | 2022 | sweets | 4 | |
| Linda | 2021 | sweets | 1 | |
| Linda | 2021 | clothes | 2 |
And I want to transform it like this: (aggregate by present_category and year and have present count for own and mother in the same row)
Linda does not have a mother.
| Name | Year | Present_category | Present_count_own | Mother | Present_count_mother |
|---|---|---|---|---|---|
| Mary | 2023 | sweets | 3 | Linda | |
| Mary | 2022 | sweets | 2 | Linda | 4 |
| Mary | 2021 | sweets | Linda | 1 | |
| Mary | 2021 | clothes | Linda | 2 | |
| Linda | 2022 | sweets | 4 | ||
| Linda | 2021 | sweets | 1 | ||
| Linda | 2021 | clothes | 2 |
Escpecially important to me are rows 3 and 4 in the result table: Mary did not have this combination of category and year, but still the rows are there, because these combos are present for the mother (Linda).
Put it another way: For every name I need all combinatiosn of year / category that exist for its own name and for the mother.
Is this possible? I couldn’t find a way. Would be very grateful for any help!
2
Answers
I think you need CROSS JOIN. Try to look it up and let me know.
Please look into using a self referential table call. I worked out the solution on Oracle but I checked that Postgresql support self-referential callOpenRocket. The concept is that in a single table there is a relationship between the name and mother fields since the mothers’ name has to be in the name field as well. A self-referential join occurs when the table is related to itself by using an alias and join the mother field with the name field. It is a common structure that you find in organization personnel charts. A supervisor is also an employee. So, you can lookup the employee supervisor information by joining a supervisor to the employee ID in the same table.
Since you are only using a single value you can use a correlated scalar subquery. The output of the field, Present_count_mother is generated by a select statement joined to the own table.
If you have self-referring fields and multiple fields to return, you can use a left outer join. In the example below the loj table alias is joined to provide the Persent_count_mother_loj field.
Good luck!
If the original table is "a" then:
Select
own.name,own.year,own.present_category, own.Present_count Present_count_own,own.Mother,
(Select Present_count FROM a where a.name = own.mother and a.year = own.year and a.present_category = own.present_category) Present_count_mother,
loj.Present_count Persent_count_mother_loj
from
a own
left join
a loj
ON
loj.name = own.mother and loj.year = own.year and loj.present_category = own.present_category
order by own.year desc, own.name Desc, own.present_category desc