|
Home > Archive > microsoft.public.sqlserver.server > November 2002 > Hierarchical query problem
You are viewing an archived Text-only version of the thread.
To view this thread in it's original format and/or if you want to reply to
this thread please [click here]
| Author |
Hierarchical query problem
|
|
| harvinder 2002-11-19, 1:23 pm |
| Hi,
We have a hierarchical query and we have to find sum
(amount) of only those rows which are involved in parent
child relationship.
table a has following data and contains hierarchical
information:
TAB A
-----
col1 parent_col1
10 20
20 null
25 null
30 null
TAB B
------
co1 amount co3
1 100 10
2 200 25
3 300 10
4 200 20
5 100 30
We have to find sum(amount,count(*) from TAB B group by
co3 where co3 is referencing to TAB A
and co3 value is involved in parent child
relationshop ...in our case 10,20
How to write a query to get results like:
co3 amount count
10 400 2
20 200 1
Thanks
--Harvinder
| |
| linda deng[MS] 2002-11-21, 12:24 am |
| Hi Harvinder,
If I understand you correctly, please try the following sample code.
-- Create the two test tables
create table table_a (col1 int, parent_col1 int)
create table table_b (col1 int, amount int, col3 int)
-- Insert some test data into the two tables
insert into table_a values (10,20)
insert into table_a(col1) values (20)
insert into table_a(col1) values (25)
insert into table_a(col1) values (30)
insert into table_b values(1,100,10)
insert into table_b values(2,200,25)
insert into table_b values(3,300,10)
insert into table_b values(4,200,20)
insert into table_b values(5,100,30)
-- Query the data
select col3, sum(amount) as amount, count(col3) as count
from table_b
where col3 in (select x.col1 from table_a x, table_a y where x.parent_col1
is not NULL or x.col1=y.parent_col1)
group by col3
-- Result
col3 amount count
----------- ----------- -----------
10 400 2
20 200 1
(2 row(s) affected)
If anything is unclear, please let me know.
Sincerely,
Linda Deng
Microsoft Corporation
This posting is provided "AS IS" with no warranties, and confers no rights.
|
|
|
|
|