aku menggunakan database akses 2000, untuk jalankan SQL ini menggunakan 1. adodc dan grid (vb) 2. crystal sql designer (crystal report)
terima kasih siswanto --- In [email protected], Aksan Kurdin <[EMAIL PROTECTED]> wrote: > Back End apa yang anda pakai ? > Kenapa setiap penulisan field selalu anda tutup dengan quotes > apostrove tunggal ? > Bukankah yang berada dalam quotes menandakan nilai, bukan field/variable ? > > Perintah > SELECT > > A.`CODE_B`,A.`ACCOUNT`,A.`YEAR1`, > > SUM(`AMOUNTT`) AS AMOUNT,SUM(`AMOUNT_BALANCEE`) AS AMOUNT_BALANCE > > dengan error seperti yang disertakan menandakan bahwa klausa CODE_B > belum di group. Setiap ada fungsi agregat seperti SUM, COUNT, dll, > maka klausa GROUP BY harus hadir untuk menentukan berdasarkan field > apa saja dilakukan SUMmary atau COUNT, atau yg lain. > > > SELECT > > A.`CODE_B`,A.`ACCOUNT`,A.`YEAR1`, > > SUM(`AMOUNTT`) AS AMOUNT,SUM(`AMOUNT_BALANCEE`) AS AMOUNT_BALANCE > FROM .... > GROUP BY A.`CODE_B`,A.`ACCOUNT`,A.`YEAR1` > > > Aksan Kurdin > > On 7/22/05, siswanto <[EMAIL PROTECTED]> wrote: > > 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 > > > > > > > > > > > > > > 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/
