Is this SQL possible?

Advertisement
Sorry guys I mis-pasted and I can't edit my post (FOUND THE EDIT), sorry about the mistake.... Here is the post, hopefully correctly this time.
I am using Analytical functions on a data set similar to this:
ID_VAL, IND_TYP, AMT
123, X, 500
123, X, 700
123, Y, 47
123, Y, 15
245, X, 32
What I want to return is this:
ID_VAL,
COUNT OF IND_TYP = 'X',
SUM OF AMT WHERE IND_TYP = 'X'
COUNT OF IND_TYP = 'Y',
SUM OF AMT WHERE IND_TYP = 'Y'
I want to return this query all in one row so the result would look like this:
123, 2, 1200, 2, 62
245, 1, 32, 0, 0
Right now I can only get them seperated:
123, X, 2, 1200
123, Y, 2, 62
245, X, 1, 32
Here is the basis of my sql:
select distinct
     IDU,
     VAL,
     count(*) OVER (PARTITION BY IDU, VAL) as CNT,
     sum(NUM) OVER (PARTITION BY IDU, VAL) as SUM_CNT
from (
select '123' IDU, 'X' VAL, 500 NUM from dual
union all
select '123', 'X', 700 from dual
union all
select '123', 'Y', 47 from dual
union all
select '123', 'Y', 15 from dual
union all
select '245', 'X', 32 from dual
Message was edited by:
gpalush
Message was edited by:
gpalush
Advertisement

Replay

analytics? why?
select id_val,
  sum( decode(ind_typ,'X',1,0) ) x_cnt,
  sum( decode(ind_typ,'X',amt,0) ) x_amt,
  sum( decode(ind_typ,'Y',1,0) ) y_cnt,
  sum( decode(ind_typ,'Y',amt,0) ) y_amt
from tab
group by id_val