SQL Statement

[tapks]

HI!

I have one table called TRAN_DET with the following columns :
TranDate Date Not Null,
TranDesc varchar(50) Not Null,
TranVal decimal(10,2) Not Null,
TranSts char(1) Not Null

The TranSts column stotes either 'C' or 'D'.

I am unable to find the summary of the transaction for each date in the following manner by writting a SQL statement:

Date, Sum of TranVal for TranSts='C', Sum of TranVal for TranSts='C'

Can any body help me in this regard?

Thankx in advance.

TapKs

 

[adroit]

Hi ,

Try the following SQL Statement.

SELECT TransDate, TransSts, SUM(TransVal) AS TransactionValue
FROM TRAN_DET
GROUP BY TransDate, TransSts


If you have any issue with the query then just drop a message.

Best Wishes .

 

[tapks]

HI!

The SQL statement which you have written will display as follows:

If for a date you have got transactions with TransSts 'C' and 'D' then it will display 2 rows for that date i.e. one row for the TranSts "c' & one row for TranSts 'D'.

So this is not solving the problem.

However thanks..
TapKs

 

[r937]

adroit's query is the best solution, it is the most efficient and will not require modification

"crosstab" layout is best done by your application program

that said, the query you want is --

select TranDate
, sum(case when TranSts='C' then TranVal else 0 end)
as "Sum of TranVal for TranSts='C'"
, sum(case when TranSts='D' then TranVal else 0 end)
as "Sum of TranVal for TranSts='D'"
from TRAN_DET
group by TranDate

rudy