See my problem inquery

Advertisement
sir this is my data
whose deptid=0 that all father
Acid          deptid          amount
K2     0     0
K21     0     0
K2101     0     0
K210101     K17     1750378
K210102     K20     364578
K210103     K19     817415
K210104     K23     432684
K2102     0     0
K210201     K17     836750
K210202     K20     1993475
K210203     K19     2921924
K2103     0     0
K210301     K12     90960
K210302     K9     5500
K210303     K17     10238
K210303     K21     580
K210303     K23     2690
K2104     0     0
K210402     K25     3800
K210403     K6     3850
K22     0     0
K2201     0     0
K220101     0     0
K22010101     K18     77390
K22010102     K17     45000
K22010102     K18     1300
K220102     0     0
K22010201     K25     55800
K220103     0     0
K22010301     K21     67860
K22010302     K12     3000
K22010302     K9     3000
K220105     K5     73638
K2202     0     0
K220202     K17     43500
K220202     K19     15000
K220204     K5     35700
K2203     0     0
K220301     K15     106151
K220301     K21     90000
K220302     K15     11025
K220304     K10     2600
K220304     K12     1085
K2204     0     0
K220401     K12     50000
K220401     K15     1158860
K220401     K4     60000
K220402     K15     123500
K220403     K24     10250
K220403     K4     4000
K2205     0     0
K220501     K15     71620
K220501     K19     11000
K220501     K24     3000
K220501     K9     9453
K220502     0     0
K22050201     K27     12980
K2206     0     0
K23     0     0
K2301     0     0
K230101     K12     51854
K230101     K27     1186751
K230102     0     0
K23010201     K12     408140
K230103     0     0
K23010301     K3     454310
K23010302     K3     7500
K230104     0     0
K23010401     K22     333400
K230106     0     0
K23010601     K9     142929
K23010602     K9     4500
K230108     K34     -7491
K230108     K4     651820
K230109     0     0
K23010902     K7     81371
K230110     K10     148800
K230111     0     0
K23011101     K21     105488
K23011101     K6     104683
K230113     0     0
K23011301     K8     131608
K230114     0     0
K23011401     K15     138625
K2302     0     0
K230201     K7     12236
K230203     K12     3388
K230203     K21     1332
K230203     K27     1484
K230203     K3     2713
K230203     K4     23796
K230203     K5     2726
K230203     K6     2421
K230203     K9     14490
K230205     K4     49026
K2303     0     0
K230301     K27     4000
K230301     K4     178370
K230302     K27     5694
K2304     0     0
K230401     K27     2656
K230401     K4     54817
K230402     K4     7769
K230403     K27     2461
K2305     0     0
K230503     K4     236665
K230504     K16     7659
K230504     K4     3526
K2306     0     0
K230601     K12     1450
K230601     K13     7900
K230601     K15     3855
K230601     K16     830
K230601     K17     2000
K230601     K4     65763
K230601     K8     25325
K230602     K3     675
K230602     K4     26909
K230603     K4     251910
K230604     K10     18050
K230604     K12     25900
K230604     K15     6900
K230604     K18     7700
K230604     K21     3800
K230604     K24     23000
K230604     K3     9500
K230604     K4     27297
K230604     K5     3850
K230604     K6     11950
K230604     K8     400
K230604     K9     3300
K2308     0     0
K230801     K13     138500
K230801     K4     707850
K230802     K13     3000
K230803     K13     3700
K230803     K4     3880
K2309     0     0
K230901     K10     200
K230901     K12     20707
K230901     K13     200
K230901     K15     2000
K230901     K27     91510
K230901     K3     8521
K230901     K4     2075
K230901     K5     120
K2310     0     0
K231001     K13     1170
K231001     K4     22716
K231002     K12     12742
K231002     K16     9215
K231002     K4     6961
K231002     K9     1326
K231004     K13     14335
K231004     K15     31566
K231004     K16     372787
K231004     K24     2427
K231004     K27     6414
K231004     K34     8910
K231004     K4     385266
K2311     0     0
K231101     K27     116731
K231102     K4     223605
K2312     0     0
K231202     K4     60600
K231205     K4     32000
K2313     0     0
K231301     K13     254334
K231301     K3     2255
K231301     K4     1192833
K231301     K6     11035
K231302     K13     6800
K231302     K15     3500
K231302     K8     200
K231303     K18     3200
K231303     K4     500
K231303     K6     3700
K231305     K4     14000
K231306     K13     4200
K231306     K4     2550
K231307     K4     3205
K231308     K27     65182
K231308     K4     25075
K231310     K13     5650
K231310     K4     6500
K231311     K13     117363
K231311     K3     4395
K231311     K4     8635
K231312     K4     1600
K231313     K13     80524
K231313     K34     40444.39
K231313     K4     375507
K231314     K13     42300
K231314     K4     83620
K231315     K13     95550
K2314     0     0
K231401     K27     38370
K231401     K4     39102
K2315     K3     4000
K2316     0     0
K231602     K4     35000
K2318     K12     667
K2318     K15     10000
K2318     K8     19508
K2319     0     0
K231903     K13     3220
K231903     K23     18000
K231903     K3     4000
K231903     K4     55676
K231903     K7     153250
K231904     K13     5400
K231904     K4     6500
K231905     K13     53978
K231905     K16     26200
K231905     K4     122350
K231906     K22     990
K231906     K27     3990
K231906     K4     469644
K231906     K9     11000
K231907     K4     2000
K2320     0     0
K232001     K3     1012657
K2321     K3     2150
K2321     K8     7824
K2322     0     0
K232202     K10     3000
K232202     K13     3200
K232202     K25     35
K232202     K27     550
K232202     K3     1580
K232202     K4     35654
K232202     K5     882
K232206     K16     25160
sir my need is
all child amount sum and show in father group by deptid
such as acid K2 is a father all those amount sum that have start 2 word is K2 group by deptid
such as
sir I use case but that give only sum amount or only one value not give all deptid wise sum result
select deptid,acid,
case when deptid=0 then
(select sum(amount) from
(SELECT accid,'0' AS deptid,0 AS amount FROM CHARTOFACC WHERE SUBSTR(CHARTOFACC.accid,1,2)='K2' AND fstatus=1
UNION
SELECT mfa.accid,mfa.deptid,mfa.amount FROM (
SELECT VOUDETAIL.deptid,VOUDETAIL.accid,NVL(SUM(debit),0)-NVL(SUM(credit),0) AS AMOUNT
FROM VOUMASTER,VOUDETAIL,DEPARTMENTMFA WHERE VOUMASTER.vno=VOUDETAIL.vno AND SUBSTR(VOUDETAIL.accid,1,2)='K2' AND VOUDETAIL.deptid=DEPARTMENTMFA.deptid
AND VOUMASTER.entdate BETWEEN '01-jul-2006' AND '30-jun-2007'
GROUP BY VOUDETAIL.deptid,VOUDETAIL.accid) mfa)
where substr(acid,1,2)=’K2’ group by deptid)
from (SELECT accid,'0' AS deptid,0 AS amount FROM CHARTOFACC WHERE SUBSTR(CHARTOFACC.accid,1,2)='K2' AND fstatus=1
UNION
SELECT mfa.accid,mfa.deptid,mfa.amount FROM (
SELECT VOUDETAIL.deptid,VOUDETAIL.accid,NVL(SUM(debit),0)-NVL(SUM(credit),0) AS AMOUNT
FROM VOUMASTER,VOUDETAIL,DEPARTMENTMFA WHERE VOUMASTER.vno=VOUDETAIL.vno AND SUBSTR(VOUDETAIL.accid,1,2)='K2' AND VOUDETAIL.deptid=DEPARTMENTMFA.deptid
AND VOUMASTER.entdate BETWEEN '01-jul-2006' AND '30-jun-2007'
GROUP BY VOUDETAIL.deptid,VOUDETAIL.accid) mfa
Acid          deptid          amount
K2          K1          334
K2          K2          3111
K2          K3          748
K2          K4          33422
K2          K5          33411
K21          K1          334
K21          K2          3111
K21          K3          748
K21          K4          33422
K21          K5          33411
K2101          K1          334
K2101          K2          3111
K2101          K3          748
K2101          K4          33422
K2101          K5          33411
K2201          K1          334
K2201          K2          3111
K2201          K3          748
K2201          K4          33422
K2201          K5          33411
Sir how I get child result in father group by deptid
Can I use loop in query that run dept table and sum dept wise result
Please give me idea bow I get resutl
thank's
Advertisement

Replay

meaby a hint I can give you is you can do a grouping by on substr. example:
create table t(v varchar2(30), n number);
insert into t values('K201',5);
insert into t values ('K202', 10);
select sum(n)
from t
group by substr(v,0,3); => result: 15
But actually I think you need to rethink your data structure. Add a parent_id so you can use hierarchical queries, ...