Re: need one query

[Joerg Bruehe]

Hi Venu, all!


VenuGopal Papasani wrote:

Hi all,
  I Have a table with followin structure where i have orgunit and parent
and value


orgunitparent   value

12   10  x
1512 y
1612 z
1712 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

--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

need one query

[VenuGopal Papasani]

Hi all,
  I Have a table with followin structure where i have orgunit and parent
and value


orgunitparent   value

12   10  x
1512 y
1612 z
1712 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.

Thanks and regards,
venu.

Re: need one query

[Peter Brawley]

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.


SELECT parent, parent + SUM(orgunit)
FROM table 
GROUP BY parent;

PB

-


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.
  
  
Thanks and regards,
  
venu.
  
  
  

No virus found in this incoming message.
Checked by AVG Free Edition.
Version: 7.1.394 / Virus Database: 268.9.8/380 - Release Date: 6/30/2006
  



No virus found in this outgoing message.
Checked by AVG Free Edition.
Version: 7.1.394 / Virus Database: 268.9.8/380 - Release Date: 6/30/2006


-- 
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]