Oracle hierarchical query: aggregate over each node's descendants, give results in tree pre-orderhierarchical...
Why the "ls" command is showing the permissions of files in a FAT32 partition?
Grepping string, but include all non-blank lines following each grep match
Mimic lecturing on blackboard, facing audience
Why do Radio Buttons not fill the entire outer circle?
El Dorado Word Puzzle II: Videogame Edition
Quoting Keynes in a lecture
Personal or impersonal in a technical resume
Alignment of six matrices
Review your own paper in Mathematics
How do you justify more code being written by following clean code practices?
How to preserve electronics (computers, iPads and phones) for hundreds of years
Make a Bowl of Alphabet Soup
Limit max CPU usage SQL SERVER with WSRM
How do I prevent inappropriate ads from appearing in my game?
Language involving irrational number is not a CFL
Do I have to know the General Relativity theory to understand the concept of inertial frame?
Proving an identity involving cross products and coplanar vectors
ContourPlot — How do I color by contour curvature?
I'm just a whisper. Who am I?
Given this phrasing in the lease, when should I pay my rent?
Do I have to take mana from my deck or hand when tapping a dual land?
What (the heck) is a Super Worm Equinox Moon?
What is this high flying aircraft over Pennsylvania?
Check if object is null and return null
Oracle hierarchical query: aggregate over each node's descendants, give results in tree pre-order
hierarchical query in oracleOracle hierarchical query question (start with … connect by … )Oracle: How do I query a Hierarchical table?Parent-Child Tree Hierarchical ORDEROracle Hierarchical query: with two node attributes NodeId and NodeTypeHow can I optimize a hierarchical query with multiple paths from child to root Oracle SQL?Avoid repeated traversing of hierarchical data when parsing a big tree frequentlyHow to keep history for a one-to-many relationship?Simulate pipelined order by in oracle 11gWhy does Oracle yield multiple levels per row in this hierarchical query?
Given hierarchical data, for each item I need to get the sum of a column over the sub-tree rooted at the item, and I need the results in pre-order.
Example: for the data
employee_id | manager_id | salary
------------|------------|-------
1 | null | 1000
2 | 1 | 100
3 | 1 | 100
4 | 3 | 10
I need the results
employee_id | total_salary
------------|-------------
1 | 1210
2 | 100
3 | 110
4 | 10
Oracle docs give the following example:
SELECT name, SUM(salary) "Total_Salary" FROM (
SELECT CONNECT_BY_ROOT last_name as name, Salary
FROM employees
WHERE department_id = 110
CONNECT BY PRIOR employee_id = manager_id)
GROUP BY name;
But I also need the rows in tree pre-order, i.e each row should have its descendants immediately after it. Is there an elegant way to do this, since a simple CONNECT BY ... START WITH ... generally does give its results in pre-order?
oracle order-by hierarchy
asked Aug 31 '18 at 15:03
laurtlaurt
14817
bumped to the homepage by Community♦ 5 mins ago
This question has answers that may be good or bad; the system has marked it active so that they can be reviewed.
add a comment |
Given hierarchical data, for each item I need to get the sum of a column over the sub-tree rooted at the item, and I need the results in pre-order.
Example: for the data
employee_id | manager_id | salary
------------|------------|-------
1 | null | 1000
2 | 1 | 100
3 | 1 | 100
4 | 3 | 10
I need the results
employee_id | total_salary
------------|-------------
1 | 1210
2 | 100
3 | 110
4 | 10
Oracle docs give the following example:
SELECT name, SUM(salary) "Total_Salary" FROM (
SELECT CONNECT_BY_ROOT last_name as name, Salary
FROM employees
WHERE department_id = 110
CONNECT BY PRIOR employee_id = manager_id)
GROUP BY name;
But I also need the rows in tree pre-order, i.e each row should have its descendants immediately after it. Is there an elegant way to do this, since a simple CONNECT BY ... START WITH ... generally does give its results in pre-order?
oracle order-by hierarchy
asked Aug 31 '18 at 15:03
laurtlaurt
14817
bumped to the homepage by Community♦ 5 mins ago
This question has answers that may be good or bad; the system has marked it active so that they can be reviewed.
add a comment |
Given hierarchical data, for each item I need to get the sum of a column over the sub-tree rooted at the item, and I need the results in pre-order.
Example: for the data
employee_id | manager_id | salary
------------|------------|-------
1 | null | 1000
2 | 1 | 100
3 | 1 | 100
4 | 3 | 10
I need the results
employee_id | total_salary
------------|-------------
1 | 1210
2 | 100
3 | 110
4 | 10
Oracle docs give the following example:
SELECT name, SUM(salary) "Total_Salary" FROM (
SELECT CONNECT_BY_ROOT last_name as name, Salary
FROM employees
WHERE department_id = 110
CONNECT BY PRIOR employee_id = manager_id)
GROUP BY name;
But I also need the rows in tree pre-order, i.e each row should have its descendants immediately after it. Is there an elegant way to do this, since a simple CONNECT BY ... START WITH ... generally does give its results in pre-order?
oracle order-by hierarchy
asked Aug 31 '18 at 15:03
laurtlaurt
14817
Given hierarchical data, for each item I need to get the sum of a column over the sub-tree rooted at the item, and I need the results in pre-order.
Example: for the data
employee_id | manager_id | salary
------------|------------|-------
1 | null | 1000
2 | 1 | 100
3 | 1 | 100
4 | 3 | 10
I need the results
employee_id | total_salary
------------|-------------
1 | 1210
2 | 100
3 | 110
4 | 10
Oracle docs give the following example:
SELECT name, SUM(salary) "Total_Salary" FROM (
SELECT CONNECT_BY_ROOT last_name as name, Salary
FROM employees
WHERE department_id = 110
CONNECT BY PRIOR employee_id = manager_id)
GROUP BY name;
But I also need the rows in tree pre-order, i.e each row should have its descendants immediately after it. Is there an elegant way to do this, since a simple CONNECT BY ... START WITH ... generally does give its results in pre-order?
oracle order-by hierarchy
oracle order-by hierarchy
asked Aug 31 '18 at 15:03
laurtlaurt
14817
asked Aug 31 '18 at 15:03
laurtlaurt
14817
asked Aug 31 '18 at 15:03
laurtlaurt
14817
asked Aug 31 '18 at 15:03
laurtlaurt
14817
asked Aug 31 '18 at 15:03
laurtlaurt
14817
14817
bumped to the homepage by Community♦ 5 mins ago
This question has answers that may be good or bad; the system has marked it active so that they can be reviewed.
bumped to the homepage by Community♦ 5 mins ago
This question has answers that may be good or bad; the system has marked it active so that they can be reviewed.
add a comment |
add a comment |
1 Answer
1
active
oldest
votes
This seems to be a working solution (I still wonder if there's a more elegant one):
select name, sum(salary) from (
select rownum rnum, name, employee_id join_id
from employees
connect by manager_id=prior employee_id
start with manager_id is null
order siblings by name
)
join (
select salary, connect_by_root employee_id ancestor_id
from employees
connect by manager_id=prior employee_id
) on join_id=ancestor_id
group by rnum, name
order by rnum;
answered Aug 31 '18 at 19:18
laurtlaurt
14817
add a comment |
Your Answer
StackExchange.ready(function() {
var channelOptions = {
tags: "".split(" "),
id: "182"
};
initTagRenderer("".split(" "), "".split(" "), channelOptions);
StackExchange.using("externalEditor", function() {
// Have to fire editor after snippets, if snippets enabled
if (StackExchange.settings.snippets.snippetsEnabled) {
StackExchange.using("snippets", function() {
createEditor();
});
}
else {
createEditor();
}
});
function createEditor() {
StackExchange.prepareEditor({
heartbeatType: 'answer',
autoActivateHeartbeat: false,
convertImagesToLinks: false,
noModals: true,
showLowRepImageUploadWarning: true,
reputationToPostImages: null,
bindNavPrevention: true,
postfix: "",
imageUploader: {
brandingHtml: "Powered by u003ca class="icon-imgur-white" href="https://imgur.com/"u003eu003c/au003e",
contentPolicyHtml: "User contributions licensed under u003ca href="https://creativecommons.org/licenses/by-sa/3.0/"u003ecc by-sa 3.0 with attribution requiredu003c/au003e u003ca href="https://stackoverflow.com/legal/content-policy"u003e(content policy)u003c/au003e",
allowUrls: true
},
onDemand: true,
discardSelector: ".discard-answer"
,immediatelyShowMarkdownHelp:true
});
}
});
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
StackExchange.ready(
function () {
StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fdba.stackexchange.com%2fquestions%2f216430%2foracle-hierarchical-query-aggregate-over-each-nodes-descendants-give-results%23new-answer', 'question_page');
}
);
Post as a guest
Required, but never shown
1 Answer
1
active
oldest
votes
1 Answer
1
active
oldest
votes
active
oldest
votes
active
oldest
votes
This seems to be a working solution (I still wonder if there's a more elegant one):
select name, sum(salary) from (
select rownum rnum, name, employee_id join_id
from employees
connect by manager_id=prior employee_id
start with manager_id is null
order siblings by name
)
join (
select salary, connect_by_root employee_id ancestor_id
from employees
connect by manager_id=prior employee_id
) on join_id=ancestor_id
group by rnum, name
order by rnum;
answered Aug 31 '18 at 19:18
laurtlaurt
14817
add a comment |
This seems to be a working solution (I still wonder if there's a more elegant one):
select name, sum(salary) from (
select rownum rnum, name, employee_id join_id
from employees
connect by manager_id=prior employee_id
start with manager_id is null
order siblings by name
)
join (
select salary, connect_by_root employee_id ancestor_id
from employees
connect by manager_id=prior employee_id
) on join_id=ancestor_id
group by rnum, name
order by rnum;
answered Aug 31 '18 at 19:18
laurtlaurt
14817
add a comment |
This seems to be a working solution (I still wonder if there's a more elegant one):
select name, sum(salary) from (
select rownum rnum, name, employee_id join_id
from employees
connect by manager_id=prior employee_id
start with manager_id is null
order siblings by name
)
join (
select salary, connect_by_root employee_id ancestor_id
from employees
connect by manager_id=prior employee_id
) on join_id=ancestor_id
group by rnum, name
order by rnum;
answered Aug 31 '18 at 19:18
laurtlaurt
14817
This seems to be a working solution (I still wonder if there's a more elegant one):
select name, sum(salary) from (
select rownum rnum, name, employee_id join_id
from employees
connect by manager_id=prior employee_id
start with manager_id is null
order siblings by name
)
join (
select salary, connect_by_root employee_id ancestor_id
from employees
connect by manager_id=prior employee_id
) on join_id=ancestor_id
group by rnum, name
order by rnum;
answered Aug 31 '18 at 19:18
laurtlaurt
14817
answered Aug 31 '18 at 19:18
laurtlaurt
14817
answered Aug 31 '18 at 19:18
laurtlaurt
14817
answered Aug 31 '18 at 19:18
laurtlaurt
14817
14817
add a comment |
add a comment |
Thanks for contributing an answer to Database Administrators Stack Exchange!
- Please be sure to answer the question. Provide details and share your research!
But avoid …
- Asking for help, clarification, or responding to other answers.
- Making statements based on opinion; back them up with references or personal experience.
To learn more, see our tips on writing great answers.
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
StackExchange.ready(
function () {
StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fdba.stackexchange.com%2fquestions%2f216430%2foracle-hierarchical-query-aggregate-over-each-nodes-descendants-give-results%23new-answer', 'question_page');
}
);
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
