terima kasih Bang Aksan, SQL dibawah ini benar dg hasil sbb: SELECT SUM(`AMOUNTT`) AS AMOUNT,SUM(`AMOUNT_BALANCEE`) AS AMOUNT_BALANCE FROM (SELECT A.`CODE_B` , A.`ACCOUNT` , LEFT(A.`PERIOD`,4) AS YEAR1, SUM (A.`AMOUNT`) AS AMOUNTT,0 AS AMOUNT_BALANCEE FROM `Anggaran` A WHERE A.`CODE_B`='2000000' AND A.`ACCOUNT`='722121' AND LEFT(A.`PERIOD`,4)='2005' GROUP BY A.`CODE_B`, A.`ACCOUNT`, LEFT(A.`PERIOD`,4) UNION ALL SELECT R.`CODE_B`, R.`ACCOUNT`, LEFT(R.`PERIOD`,4) AS YEAR1, 0 AS AMOUNTT,SUM (R.`AMOUNT_BALANCE`) AS AMOUNT_BALANCEE FROM `Realisasi` R WHERE R.`CODE_B`='2000000' AND R.`ACCOUNT`='722121' AND LEFT(R.`PERIOD`,4)='2005' GROUP BY R.`CODE_B`, R.`ACCOUNT`, LEFT(R.`PERIOD`,4)) GROUP BY 1,2,3
hasil akhir AMOUNT AMOUNT_BALANCE 1.140.000,00 10.500.000,00 tetapi terjadi error bila aku tambah sbb: SELECT A.`CODE_B`,A.`ACCOUNT`,A.`YEAR1`, SUM(`AMOUNTT`) AS AMOUNT,SUM(`AMOUNT_BALANCEE`) AS AMOUNT_BALANCE .... errornya sbb: 'You tried to execute a query that does not include the specified expression 'CODE_B' as part of an aggregate function.' apa yang kurang dg SQL diatas. terima kasih responnya. siswanto --- In [email protected], 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 <*> 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/
