require select statment to create view 1

[Crystalboy1]

i got a table inwhich data is stored in this manner details given below.

Date Advances tradetype
20080801 100007.04 pwsweep
20080802 234567.56 pwrefund
20080802 237564.56 pwsweep
20080802 100.56 pwnlf-p
20080803 3000 pwnlf-d
20080803 300022 pwnlf-p

There are total of 5 types of tradetypes.

i want data stored in a view in the way details, example given below.

date advances writeoff other
20080801 2345566666 22222433 665456544
20080802 2345566666 22222433 665456544
20080803 2345566666 22222433 665456544

Please remember all the above figures are just examples are not the actual totoals or figures.

now the formulus to calculate advance is
to sum all advances the cash_trades tables group by date where tradetype = pwsweep,pwrefund,pwnlf-p

now the formulus to calculate writeoff is
to sum all advances the cash_trades tables group by date where tradetype = pwsweep,pwnlf-d

now the formulus to calculate other is
to sum all advances the cash_trades tables group by date where tradetype = pwrefund

hope someone wil help me with the select statement in the body of the view.

 

[LKBrwnDBA]

Check out the SUM(), DECODE() (or CASE..WHEN) functions.

----------------------------------------------------------------------------
The person who says it can't be done should not interrupt the person doing it. -- Chinese proverb

 

[SantaMufasa]

First, CrystalBoy, you cannot have a column named DATE...it is a reserved Oracle keyword that defines a column of DATE datatype.

Second, there are probably a dozen coding techniques that would produce the results you want. Here is some code that I like, that does what you want:

SQL> select * from cash_trades;

DT          ADVANCES TRADETYPE
--------- ---------- ----------
01-AUG-08  100007.04 pwsweep
02-AUG-08  234567.56 pwrefund
02-AUG-08  237564.56 pwsweep
02-AUG-08     100.56 pwnlf-p
03-AUG-08       3000 pwnlf-d
03-AUG-08     300022 pwnlf-p

select to_char(w.dt,'yyyymmdd') "DATE", advances, writeoff, other
  from (select distinct trunc(dt) dt from cash_trades) w
      ,(select trunc(dt) dt, sum(advances) advances
          from cash_trades
         where tradetype in ('pwsweep','pwrefund','pwnlf-p')
         group by trunc(dt)) x
      ,(select trunc(dt) dt, sum(advances) writeoff
          from cash_trades
         where tradetype in ('pwsweep','pwnlf-d')
         group by trunc(dt)) y
      ,(select trunc(dt) dt, sum(advances) other
          from cash_trades
         where tradetype in ('pwrefund')
         group by trunc(dt)) z
 where w.dt = x.dt(+)
   and w.dt = y.dt(+)
   and w.dt = z.dt(+)
/

DATE       ADVANCES   WRITEOFF      OTHER
-------- ---------- ---------- ----------
20080801  100007.04  100007.04
20080802  472232.68  237564.56  234567.56
20080803     300022       3000

Let us know your response to this technique.

Mufasa
(aka Dave of Sandy, Utah, USA)
[I provide low-cost, remote Database Administration services: www.dasages.com]
“Beware of those that seek to protect you from harm or risk. The cost will be your freedoms and your liberty.”

 

[Crystalboy1]

Mufasa it worked fine. thanks a lot for your help. you saved me a lot of time. thanks once again.