Solved

# Summary query problem.

Posted on 2009-04-19
207 Views
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
``````
0
Question by:emi_sastra

LVL 25

Accepted Solution

Hi emi_sastra,

Is this what you want?

``````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
``````
0

LVL 1

Author Comment

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

emi_sastra,

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

0

LVL 1

Author Comment

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

Which one?

Thank you.
0

LVL 25

Expert Comment

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

0

LVL 1

Author Comment

I see.

Thank you very much for your help.
0

## Featured Post

### Suggested Solutions

SQL Date from a string 4 39
SQL PIVOT Table 21 30
adding % symbol to a percentage - oracle query 16 24
Calculate age in Access report 11 22
In database programming, custom sort order seems to be necessary quite often, at least in my experience and time here at EE. Within the realm of custom sorting is the sorting of numbers and text independently (i.e., treating the numbers as number…
If you have heard of RFC822 date formats, they can be quite a challenge in SQL Server. RFC822 is an Internet standard format for email message headers, including all dates within those headers. The RFC822 protocols are available in detail at:   ht…
This video discusses moving either the default database or any database to a new volume.
This video gives you a great overview about bandwidth monitoring with SNMP and WMI with our network monitoring solution PRTG Network Monitor (https://www.paessler.com/prtg). If you're looking for how to monitor bandwidth using netflow or packet s…