Hi Venu, all!
VenuGopal Papasani wrote:
> Hi all,
> I Have a table with followin structure where i have orgunit and parent
> and value
>
>
> orgunit parent value
>
> 12 10 x
> 15 12 y
> 16 12 z
> 17 12 p
>
> Now here 15,16, and 17 are the children of 12 and 12 is the
> child of 10.Now i need a query which gives the sum of all the children of
> 12 and nturn 12 itself . i.e I need the sum(values(15+16+17+12)).Is there a
> query which gives the sum of all these.It will be very helpful if i am able
> to get it in a single query.
Sure there is:
SELECT SUM(value) FROM his_table WHERE (orgunit = 12 OR parent = 12)
This is fairly basic SQL, nothing tricky involved.
This does not descend into deeper hierarchies directly, for that you
would need a more elaborate statement.
If you have to deal with deeper hierarchies, you might search for the
"nested set model" which makes it easier to work on them than the
traditional "parent pointer" approach you have used here.
Sorry, the only URL I have ready is in German, which may not help you much.
HTH,
Joerg
--
Joerg Bruehe, Senior Production Engineer
MySQL AB, www.mysql.com
