# 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