[HELP] SQL query in query

[Braincode]

Agan...agan sekalian yang jago sql tulungin ane donk, ane ada kesulitan waktu latihan bikin query dalam query nih.



ini isi table-nya ketika ane buka "select * from fx_bin_trx_rpt" :


Code:

CARD_BIN TRX_GROUP_ID DATE TRX_AMOUNT TRX_CURRENCY TRX_FREQ
---------- ------------ ---------- -------------------- ------------ -----------
52xxxx 1 05/26/2010 54500 IDR 18740
55xxxx 1 05/26/2010 156050 IDR 1839
58xxxx 1 05/26/2010 6685 IDR 69
52xxxx 1 05/27/2010 203310 IDR 2821
55xxxx 1 05/27/2010 23710 IDR 274
58xxxx 1 05/27/2010 17150 IDR 11
52xxxx 1 05/28/2010 600060 IDR 8598
55xxxx 1 05/28/2010 33285 IDR 408
58xxxx 1 05/28/2010 17150 IDR 18
52xxxx 1 05/29/2010 7229550 IDR 9413

maaf yang gw buat latihan ini ternyata data rahasia
nah ini yang udah ane lakuin sama query-nya sehingga ter-group dan ter-order:

SELECT TRX_GROUP_ID, SUM(TRX_FREQ) AS TRX_FREQ, SUM(TRX_AMOUNT) AS TRX_AMOUNT, TRX_CURRENCY FROM FX_BIN_TRX_RPT WHERE (DATE BETWEEN '05/26/2010' AND '05/31/2010') GROUP BY TRX_GROUP_ID, TRX_CURRENCY ORDER BY TRX_GROUP_ID

maaf agak berantakan

hasilnya seperti ini :

TRX_GROUP_ID TRX_FREQ TRX_AMOUNT TRX_CURRENCY
------------ ----------- -------------------- ------------
1 65771 484124 IDR
1 1 10 USD
2 3196 260000 IDR
3 18792 1170000 IDR
4 2 3600 AED
4 10 1100 CNY
4 1 260 GBP
4 6 11000 HKD
4 681 450190 IDR
4 2 550 JPY
4 25 19300 MYR
4 3 4000 SAR
4 27 9100 SGD
4 3 23950 THB
4 1 40 USD
5 2732 196920 IDR
6 8152 450000 IDR
7 1375 1120000 IDR
8 9985 36525 IDR
8 11 63545 USD
9 15146 3948056 IDR
9 110 294917 USD
10 15 39100 IDR
11 148 106300 IDR
12 3744 1035654 IDR
14 71 275000 IDR
15 921 2630270 IDR
16 17 90000 IDR
17 97 383652 IDR
18 1064 499341 IDR
19 3537 137618 IDR
20 7 29000 IDR
21 199 418808 IDR
23 357 12608 IDR
24 1 2000 CNY
24 1 2060 DKK
24 1758 55676 IDR
24 6 2098 MYR
24 8 4588 SGD
24 1 15000 THB
25 76167 0 IDR
25 305 0 USD
26 1247 0 IDR
27 6724 0 IDR
28 10 0 CNY
28 1 0 EGP
28 2 0 HKD
28 260 0 IDR
28 2 0 KRW
28 11 0 MYR
28 2 0 SAR
28 3 0 SGD
29 1054 0 IDR
29 3 0 USD
30 3567 0 IDR
30 4 0 USD
31 528 0 IDR
32 65 34300 USD
33 368 10218 IDR
34 452 0 IDR

60 record(s) selected.

maaf gan kalo berantakan

nah yang ane pingin tuh tadinya querynya kaya begini :




Code:

SELECT TRX_GROUP_ID, SUM(SELECT TRX_FREQ FROM FX_BIN_TRX_RPT WHERE CARD_BIN='526414') AS TRX_FREQ_526414, SUM(SELECT TRX_AMOUNT FROM FX_BIN_TRX_RPT WHERE CARD_BIN='526414') AS TRX_AMOUNT_526414, TRX_CURRENCY FROM FX_BIN_TRX_RPT GROUP BY TRX_GROUP_ID, TRX_CURRENCY ORDER BY TRX_GROUP_ID

yang ane pingin tuh table-nya kaya gini :



TRX_GROUP_ID TRX_FREQ_526414 TRX_AMOUNT_526414 TRX_CURRENCY

----------------- -------------------- ------------------------ -----------------





tapi gak bisa gagal terus. emang sih gagalnya karena query di dalam query tapi query-nya didalam function "SUM".



tulungin yah gan...kalo buat agan2 ahli sql mah ini barang kecil