Home » SQL & PL/SQL » SQL & PL/SQL » see my problem in query
see my problem in query [message #197378] Wed, 11 October 2006 01:57 Go to next message
mfa786
Messages: 210
Registered: February 2006
Location: karachi
Senior Member

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

Re: see my problem in query [message #197476 is a reply to message #197378] Wed, 11 October 2006 06:55 Go to previous message
joy_division
Messages: 4640
Registered: February 2005
Location: East Coast USA
Senior Member
Please use code tags so we can read this. After all these posts, you're still too lazy to put any effort in to make it easier on us.
Previous Topic: DDL in PL/SQL => insufficient privileges [SOLVED]
Next Topic: Select set of records from a table
Goto Forum:
  


Current Time: Tue Dec 06 10:14:37 CST 2016

Total time taken to generate the page: 0.06323 seconds