Help creating query using sum and decode

[malaygal]

I have these Oracle 10G database with colums

state,version,account,jan,feb,mar,apr,may,jun,jul,aug,sep,oct,nov,dec


jan Feb

NY ACT ACCT1 100 0
NY ACT ACCT2 200 0
NY ACT ACCT3 125 0
NY BUD ACCT1 75 0
NY BUD ACCT2 150 0
NY BUD ACCT3 60 0
CA ACT ACCT1 100 0
CA ACT ACCT2 30 0
CA ACT ACCT3 20 0
CA BUD ACCT1 10 0
CA BUD ACCT2 15 0
CA BUD ACCT3 50 0
FL ACT ACCT1 200 0
FL BUD ACCT1 125 0
TX BUD ACCT2 75 0


and I am trying query the table to create a report similar to below:

Acct1 Acct2 Acct3

ACT BUD %VAR ACT BUD %VAR ACT BUD %VAR

NY 100 75 33.3 200 150 33.3 125 60 108.3
CA 100 10 900.0 30 15 100 20 50 60.0
FL 200 125 60.0
TX 75

%var is calcualted as (ACT - BUD / BUD) * 100


I have tried several query using "sum(decode" and could not come up with the correct data.

Any help will be greatly appreciated.

 

[Dagon]

Your data is very muddled. What exactly is in the jan, feb ... columns ? Is it a number ? From the data above it looks like a code of some kind. What exactly is the sum supposed to show ? Do you want to add together the 12 monthly figures (assuming there are numbers in those columns) or split them into some other grouping ?

Can you:

a) provide some create table and insert statements for tables
b) the SQL you have attempted to run so far

 

[Dagon]

Also, your formula seems to make very little sense. Taking it literally:

(ACT - BUD / BUD) * 100

since division is performed first, BUD / BUD = 1, so the formula would be:

(ACT - 1 ) * 100

Is that really what you want ?

 

[Dagon]

It's a bit of a slow day today, so I had another look at this and I think I can see what you're trying to do, although saying there are columns called jan, feb, mar.. when actually there aren't doesn't help. I'm assuming that what you mean by jan, feb etc are the rows identified by the type ACCT1, ACCT2 etc.

In that case, you should be able to do what you want using something like the following:

create table acct_data (state varchar2(10), acc_type varchar(20), acc_type_mth varchar2(20), amount1 number, amount2 number);

insert into acct_data values ('NY', 'ACT',  'ACCT1',    100,     0);
insert into acct_data values ('NY', 'ACT',  'ACCT2',   200,    0);
insert into acct_data values ('NY', 'ACT',  'ACCT3',    125,    0);
insert into acct_data values ('NY', 'BUD',  'ACCT1',    75,    0);
insert into acct_data values ('NY', 'BUD',  'ACCT2',    150,    0);
insert into acct_data values ('NY', 'BUD',  'ACCT3',    60,    0);
insert into acct_data values ('CA', 'ACT',  'ACCT1',    100,    0);
insert into acct_data values ('CA', 'ACT',  'ACCT2',    30,    0);
insert into acct_data values ('CA', 'ACT',  'ACCT3',    20,    0);
insert into acct_data values ('CA', 'BUD',  'ACCT1',    10,    0);
insert into acct_data values ('CA', 'BUD',  'ACCT2',    15,    0);
insert into acct_data values ('CA', 'BUD',  'ACCT3',    50,    0);
insert into acct_data values ('FL', 'ACT',  'ACCT1',    200,    0);
insert into acct_data values ('FL', 'BUD',  'ACCT1',    125,    0);
insert into acct_data values ('TX', 'BUD',  'ACCT2',    75,   0);

select state, 
       act_amt1,  bud_amt1, case when bud_amt1 = 0 or act_amt1 = 0 then null 
else abs(round(((act_amt1 - bud_amt1)/bud_amt1),3)*100) end as var1,
       act_amt2,  bud_amt2, case when bud_amt2 = 0 or act_amt2 = 0 then null 
else abs(round(((act_amt2 - bud_amt2)/bud_amt2),3)*100) end as var2,
	   act_amt3,  bud_amt3, case when bud_amt3 = 0 or act_amt3 = 0 then null 
else abs(round(((act_amt3 - bud_amt3)/bud_amt3),3)*100) end as var3
from
(select state, 
       sum(decode(substr(acc_type_mth,5), '1', decode(acc_type, 'ACT', amount1, 0), 0)) as act_amt1,
       sum(decode(substr(acc_type_mth,5), '1', decode(acc_type, 'BUD', amount1, 0), 0)) as bud_amt1,
       sum(decode(substr(acc_type_mth,5), '2', decode(acc_type, 'ACT', amount1, 0), 0)) as act_amt2,
       sum(decode(substr(acc_type_mth,5), '2', decode(acc_type, 'BUD', amount1, 0), 0)) as bud_amt2,
       sum(decode(substr(acc_type_mth,5), '3', decode(acc_type, 'ACT', amount1, 0), 0)) as act_amt3,
       sum(decode(substr(acc_type_mth,5), '3', decode(acc_type, 'BUD', amount1, 0), 0)) as bud_amt3
from acct_data
group by state)

 

[malaygal]

Thanks for the reply.

Hope this post is a little clearer.

The fct_table has the following fields (columns)

state,version,account,jan,feb,mar,apr,may,jun,jul,aug,sep,oct,nov,dec

This is the sample date of the fct_table.

state version account jan Feb Mar ............

NY ACT ACCT1 100 0
NY ACT ACCT2 200 0
NY ACT ACCT3 125 0
NY BUD ACCT1 75 0
NY BUD ACCT2 150 0
NY BUD ACCT3 60 0
CA ACT ACCT1 100 0
CA ACT ACCT2 30 0
CA ACT ACCT3 20 0
CA BUD ACCT1 10 0
CA BUD ACCT2 15 0
CA BUD ACCT3 50 0
FL BUD ACCT1 200 0
FL BUD ACCT1 125 0
TX BUD ACCT2 75 0


So far, I have this query that is working correctly(blank lines provided for clarity).
The only thing is the %var calculation: (ACCT1_ACT - ACCT1_BUD) / ACCT1_BUD
How can I use my as declaration in the %var calculation



select distinct state,

sum(decode(version,'ACT',(decode(account,'ACCT1',jan,null)))) as ACCT1_ACT,
sum(decode(version,'BUD',(decode(account,'ACCT1',jan,null)))) as ACCT1_BUD,

((sum(decode(version,'ACT',(decode(account,'ACCT1',jan,null)))) -
sum(decode(version,'BUD',(decode(account,'ACCT1',jan,null))))) /
sum(decode(version,'BUD',(decode(account,'ACCT1',jan,null))))) * 100,1) as %VAR1,,

-- (ACCT1_ACT - ACCT1_BUD) / ACCT1_BUD -- this is how I want to calculate


sum(decode(version,'ACT',(decode(account,'ACCT2',jan,null)))) as ACCT2_ACT,
sum(decode(version,'BUD',(decode(account,'ACCT2',jan,null)))) as ACCT2_BUD,

sum(decode(version,'ACT',(decode(account,'ACCT3',jan,null)))) as ACCT3_ACT,
sum(decode(version,'BUD',(decode(account,'ACCT3',jan,null)))) as ACCT3_BUD

from fct_table
group by state

 

[Dagon]

So for the feb, mar ... etc amounts, it is just a repetition of what you are doing for jan but using a different column ? If so, what I suggested earlier should work. You would just need to extend it to add in all the other amount columns.

As a side point, why are you putting a "select distinct" in the query when there is already a group by ? The whole point of "group by" is that it will group everything to the level of state, so rows will already be unique. I see this sort of thing quite often and always find it extremely irritating.