Summary query problem.

Hi,

I have below query.

Result :

TglTransaksi      GiroMasuk               GiroKeluar                Selisih
4/17/2009         523351920.0000       0.0000                       523351920.0000
4/18/2009         0.0000                       258517935.0000       -258517935.0000
4/18/2009         805066380.0000        0.0000                       805066380.0000

I want the same date exist just in one record.

How to do it?

Thank you.

SELECT     TglTransaksi, GiroMasuk, GiroKeluar, GiroMasuk - GiroKeluar AS Selisih
FROM         (SELECT     A.TglTransaksi, SUM(B.NilaiTransaksi) AS GiroMasuk, CAST(0 AS MONEY) AS GiroKeluar
                       FROM          THCHQRCV2009 AS A LEFT OUTER JOIN
                                              TDCHQRCV2009 AS B ON A.NoTransaksi = B.NoTransaksi
                       GROUP BY A.TglTransaksi
                       UNION ALL
                       SELECT     A.TglTransaksi, CAST(0 AS MONEY) AS GiroMasuk, SUM(B.NilaiTransaksi) AS GiroKeluar
                       FROM         THCHQPAY2009 AS A LEFT OUTER JOIN
                                             TDCHQPAY2009 AS B ON A.NoTransaksi = B.NoTransaksi
                       GROUP BY A.TglTransaksi) AS T1
GROUP BY TglTransaksi, GiroMasuk, GiroKeluar

Open in new window

LVL 1
emi_sastraAsked:
Who is Participating?
 
lwadwellConnect With a Mentor Commented:
Hi emi_sastra,

Is this what you want?


lwadwell
SELECT     TglTransaksi, SUM(GiroMasuk) AS GiroMasuk, SUM(GiroKeluar) AS GiroKeluar, SUM(GiroMasuk - GiroKeluar) AS Selisih
FROM         (SELECT     A.TglTransaksi, SUM(B.NilaiTransaksi) AS GiroMasuk, CAST(0 AS MONEY) AS GiroKeluar
                       FROM          THCHQRCV2009 AS A LEFT OUTER JOIN
                                              TDCHQRCV2009 AS B ON A.NoTransaksi = B.NoTransaksi
                       GROUP BY A.TglTransaksi
                       UNION ALL
                       SELECT     A.TglTransaksi, CAST(0 AS MONEY) AS GiroMasuk, SUM(B.NilaiTransaksi) AS GiroKeluar
                       FROM         THCHQPAY2009 AS A LEFT OUTER JOIN
                                             TDCHQPAY2009 AS B ON A.NoTransaksi = B.NoTransaksi
                       GROUP BY A.TglTransaksi) AS T1
GROUP BY TglTransaksi

Open in new window

0
 
emi_sastraAuthor Commented:
Hi lwadwell,

The changed code is below right?

SELECT     TglTransaksi, SUM(GiroMasuk) AS GiroMasuk, SUM(GiroKeluar) AS GiroKeluar, SUM(GiroMasuk - GiroKeluar) AS Selisih

Thank you.
0
 
lwadwellCommented:
emi_sastra,

Yes and removed the two columns now being summed from the GROUP BY.

lwadwell
0
Upgrade your Question Security!

Your question, your audience. Choose who sees your identity—and your question—with question security.

 
emi_sastraAuthor Commented:
removed the two columns now being summed from the GROUP BY.

Which one?

Thank you.
0
 
lwadwellCommented:
emi_sastra,

SELECT     TglTransaksi, GiroMasuk, GiroKeluar, GiroMasuk - GiroKeluar AS Selisih
became
SELECT     TglTransaksi, SUM(GiroMasuk) AS GiroMasuk, SUM(GiroKeluar) AS GiroKeluar, SUM(GiroMasuk - GiroKeluar) AS Selisih

and

GROUP BY TglTransaksi, GiroMasuk, GiroKeluar
became
GROUP BY TglTransaksi

lwadwell
0
 
emi_sastraAuthor Commented:
I see.

Thank you very much for your help.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.