List:General Discussion« Previous MessageNext Message »
From:Joerg Bruehe Date:July 4 2006 1:31pm
Subject:Re: need one query
View as plain text  
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
Thread
need one queryVenuGopal Papasani1 Jul
  • Re: need one queryPeter Brawley1 Jul
  • Re: need one queryJoerg Bruehe4 Jul