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/
