coba query dibawah ini, benar kata sdr aksan: SELECT CODE_B,ACCOUNT,YEAR,SUM(AMOUNT_A),SUM(AMOUNT_R) FROM (SELECT A.`CODE_B`, A.`ACCOUNT`, A.`YEAR`, A.`AMOUNT` AS AMOUNT_A, '0' AS AR FROM `Anggaran` A WHERE A.`CODE_B`='2000000' AND A.`ACCOUNT`='722121' UNION SELECT R.`CODE_B`, R.`ACCOUNT`, R.`YEAR`, R.`AMOUNT_BALANCE` AS AMOUNT_R, '1' AS AR FROM `Realisasi` R WHERE R.`CODE_B`='2000000' AND R.`ACCOUNT`='722121') GROUP BY `CODE_B`, `ACCOUNT`, `YEAR`
On Wed, 20 Jul 2005 03:31:50 -0700 Aksan Kurdin <[EMAIL PROTECTED]> wrote: > (QueryA) > UNION > (QUERYB) > > tidak akan menghasilkan distinct summary record, anda >masih harus > menjumlahkannya lagi: > > SELECT a,b,c,sum(d),sum(e) >FROM( > (QueryA) > UNION > (QUERYB) > ) > GROUP BY a,b,c > > Kalau anda teliti melihat sql yang saya berikan, anda >masih kurang > satu step lagi, yang anda buat baru sub query untuk >mendapatkan > summarynya saja. > > > Aksan Kurdin > > On 7/20/05, siswanto <[EMAIL PROTECTED]> wrote: >> terima kasih sumbangsihnya, >> >> setelah aku coba dg SQL command >> SELECT >> A.`CODE_B`, >> A.`ACCOUNT`, >> A.`YEAR`, >> SUM (A.`AMOUNT`) AS AMOUNT_A,0 AS AMOUNT_R >> FROM >> `Anggaran` A >> WHERE >> A.`CODE_B`='2000000' AND >> A.`ACCOUNT`='722121' >> GROUP BY >> A.`CODE_B`, >> A.`ACCOUNT`, >> A.`YEAR` >> ORDER BY >> 1,2 >> UNION >> SELECT >> R.`CODE_B`, >> R.`ACCOUNT`, >> R.`YEAR`, >> 0 AS AMOUNT_A,SUM (R.`AMOUNT_BALANCE`) AS AMOUNT_R >> FROM >> `Realisasi` R >> WHERE >> R.`CODE_B`='2000000' AND >> R.`ACCOUNT`='722121' >> GROUP BY >> R.`CODE_B`, >> R.`ACCOUNT`, >> R.`YEAR` >> ORDER BY >> 1,2 >> >> hasilnya dari SQL diatas adalah sbb; >> CODE_B ACCOUNT YEAR AMOUNT_A AMOUNT_R >> 200000 722121 2005 0,00 10.500.000,00 >> 200000 722121 2005 1.140.000,00 0,00 >> >> hasil yang saya inginkan adalah sbb: >> CODE_B ACCOUNT YEAR AMOUNT_A AMOUNT_R >> 200000 722121 2005 1.140.000,00 10.500.000,00 >> >> apa yang kurang dari SQL diatas,mohon dibantu. terima >>kasih >> responnya. >> >> siswanto >> >> >> >> --- In [email protected], Aksan Kurdin >> <[EMAIL PROTECTED]> wrote: >> > Saya belum mencobanya, tetapi coba query berikut. >> > Intinya anda summary dulu tabel A, sediakan field >>kosong berisi 0 >> > untuk jumlah summary tabel B, demikian juga untuk >>tabel B. >> > Dari keduanya baru di union-kan, dan di summary lagi. >> > >> > SELECT A.Account, A.Year1, >> > SUM(A.AmtA) AS Amount_A, >> > SUM(A.AmtB) AS Amount_B >> > FROM ( >> > SELECT Account, Left(Period,4) AS Year1, >> > SUM(Amount_A) AS AmtA, 0 AS AmtB >> > FROM TABELA >> > WHERE Account = 721 AND Left(Period,4) = '2005' >> > GROUP BY Account, Left(Period,4) >> > >> > UNION ALL >> > >> > SELECT Account, Left(Period,4) AS Year1, >> > 0 AS AmtB, SUM(Amount_B) AS AmtB >> > FROM TABELA >> > WHERE Account = 721 AND Left(Period,4) = '2005' >> > GROUP BY Account, Left(Period,4) >> > ) AS A >> > GROUP BY Account, Year1 >> > >> > On 7/17/05, siswanto <[EMAIL PROTECTED]> wrote: >> > > met sore semuanya, >> > > >> > > mohon dibantu aku ada masalah dg SQL command, >> > > TABEL A >> > > account period amount_A >> > > 721 200501 1000 >> > > 721 200502 2000 >> > > 721 200503 3000 >> > > 721 200504 4000 >> > > 721 200505 5000 >> > > 721 200506 6000 >> > > 721 200507 7000 >> > > 721 200508 8000 >> > > 721 200509 9000 >> > > 721 200510 10000 >> > > 721 200511 11000 >> > > 721 200512 12000 >> > > >> > > TABEL B >> > > account period amount_B >> > > 721 200501 3000 >> > > 721 200502 2000 >> > > 721 200503 1000 >> > > >> > > hasil yg aku inginkan adalah sbb: >> > > account year amount_A amount_B >> > > 721 2005 78000 6000 >> > > >> > > demikian semoga mengerti apa yg aku maksudkan dan >>terima kasih >> > > tanggapannya. >> > > >> > > siswanto >> > > >> > > >> > > >> > > >> > > >> > > Untuk keluar dari millis ini, kirim email kosong ke: >> > > [EMAIL PROTECTED] >> > > >> > > >> > > Yahoo! Groups Links >> > > >> > > >> > > >> > > >> > > >> > > >> > > >> >> >> >> >> Untuk keluar dari millis ini, kirim email kosong ke: >> [EMAIL PROTECTED] >> >> >> Yahoo! Groups Links >> >> >> >> >> >> >> > > > Untuk keluar dari millis ini, kirim email kosong ke: > [EMAIL PROTECTED] > > > Yahoo! Groups Links > > > > > > ======================================================================================== Akses Internet TELKOMNet-Instan beri Diskon s.d. 50 % khusus untuk wilayah Jawa Timur. Informasi selengkapnya di www.telkomnetinstan.com atau hub 0800-1-INSTAN (467826) ======================================================================================== Untuk keluar dari millis ini, kirim email kosong ke: [EMAIL PROTECTED] Yahoo! Groups Links <*> To visit your group on the web, go to: http://groups.yahoo.com/group/Programmer-VB/ <*> To unsubscribe from this group, send an email to: [EMAIL PROTECTED] <*> Your use of Yahoo! Groups is subject to: http://docs.yahoo.com/info/terms/
