(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/
