?
Solved

Summary query problem.

Posted on 2009-04-19
6
Medium Priority
?
213 Views
Last Modified: 2012-05-06
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

0
Comment
Question by:emi_sastra
  • 3
  • 3
6 Comments
 
LVL 25

Accepted Solution

by:
lwadwell earned 2000 total points
ID: 24181915
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
 
LVL 1

Author Comment

by:emi_sastra
ID: 24182008
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
 
LVL 25

Expert Comment

by:lwadwell
ID: 24182031
emi_sastra,

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

lwadwell
0
What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

 
LVL 1

Author Comment

by:emi_sastra
ID: 24182038
removed the two columns now being summed from the GROUP BY.

Which one?

Thank you.
0
 
LVL 25

Expert Comment

by:lwadwell
ID: 24182058
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
 
LVL 1

Author Comment

by:emi_sastra
ID: 24182072
I see.

Thank you very much for your help.
0

Featured Post

Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

'Between' is such a common word we rarely think about it but in SQL it has a very specific definition we should be aware of. While most database vendors will have their own unique phrases to describe it (see references at end) the concept in common …
An alternative to the "For XML" way of pivoting and concatenating result sets into strings, and an easy introduction to "common table expressions" (CTEs). Being someone who is always looking for alternatives to "work your data", I came across this …
Whether it be Exchange Server Crash Issues, Dirty Shutdown Errors or Failed to mount error, Stellar Phoenix Mailbox Exchange Recovery has always got your back. With the help of its easy to understand user interface and 3 simple steps recovery proced…
With just a little bit of  SQL and VBA, many doors open to cool things like synchronize a list box to display data relevant to other information on a form.  If you have never written code or looked at an SQL statement before, no problem! ...  give i…
Suggested Courses

809 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question