# Thread: Calculations in select statement

1. Registered User
Join Date
Feb 2004
Location
Charlotte, NC
Posts
79

## Unanswered: Calculations in select statement

Hi

I have a problem that I am trying to resolve with the most optimized code that I can.

I need to create a query that I can select data from with VBA code, but I need it to be only a single query, as the query needs to be stored in a single field in a database.

The query needs to return 15 columns. The first 3 are simple selects from a table, the next 3 are the same, but they are filtered by the region that the state falls into. i.e. if the state is NY, then the region is 'Northeast'. This continues so that there are 4 groups of 3 columns, one for each of 4 regions of the US.

Obviously

select a, b, c from table

sorts out the first 3 columns, but the next 12 each require that the same data is returned but filtered by state. This filtering can only be done by seeing if the state is within a region defined in a second table that has states listed with their region names (4 regions in total).

Can anyone help me with this?

TIA

Michael

2. Moderator.
Join Date
Sep 2002
Location
UK
Posts
5,171

## Re: Calculations in select statement

Maybe, if you provide some sample data and sample required output. Keep it as simple as possible!

3. Registered User
Join Date
Feb 2004
Location
Charlotte, NC
Posts
79
Hi

Thanks for the reply. OK, here is the basic idea:

Table a contains the following fields:

StateName, Value1, Value2, Value3

Table b contains the following fields:

StateName, Region

The output required is the sum of values in the fields from table a:

Total1, Total2, Total3, Total1NE, Total2NE, Total3NE, Total1MW, Total2MW, Total3MW, Total1SH, Total2SH, Total3SH, Total1WT, Total2WT, Total3WT

The 4th output field and beyond are the sums of values from table a, but they are filtered according to which region the state falls into.

I hope this clarifies the problem.

Thanks

Michael

4. Moderator.
Join Date
Sep 2002
Location
UK
Posts
5,171
Yes, I think so:

select sum(value1) as Total1
, sum(value2) as Total2
, sum(value3) as Total3
, sum(DECODE(b.region,'NE', value1)) as "sum_NE_value1"
, sum(DECODE(b.region,'NE', value2)) as "sum_NE_value2"
, sum(DECODE(b.region,'NE', value3)) as "sum_NE_value3"
, sum(DECODE(b.region,'MW', value1)) as "sum_MW_value1"
, sum(DECODE(b.region,'MW', value2)) as "sum_MW_value2"
, sum(DECODE(b.region,'MW', value3)) as "sum_MW_value3"
, sum(DECODE(b.region,'SH', value1)) as "sum_SH_value1"
, sum(DECODE(b.region,'SH', value2)) as "sum_SH_value2"
, sum(DECODE(b.region,'SH', value3)) as "sum_SH_value3"
, sum(DECODE(b.region,'WT', value1)) as "sum_WT_value1"
, sum(DECODE(b.region,'WT', value2)) as "sum_WT_value2"
, sum(DECODE(b.region,'WT', value3)) as "sum_WT_value3"
from a, b
where a.statename = b.statename;

(Sorry about the column aliases being different, but I have a tool to produce this type of query and it generates the names).

5. Registered User
Join Date
Feb 2004
Location
Charlotte, NC
Posts
79
Thanks for the response. I have tried this SQL, but what I gave you earlier was a simplified version. Here is the SQL that I actually used:

select
sum(tot_ln_am)/sum(tot_ln_qt) total_us,
sum(purch_ln_am)/sum(purch_ln_qt) purchase_us,
sum(refi_ln_am/refi_ln_qt) refi_us,
sum(DECODE(pii.mrt_state_region_xref.region, 'NE', tot_ln_amt))/sum(DECODE(pii.mrt_state_region_xref.region, 'NE', tot_ln_qt)) tot_ne,
sum(DECODE(pii.mrt_state_region_xref.region, 'NE', tot_ln_amt))/sum(DECODE(pii.mrt_state_region_xref.region, 'NE', tot_ln_qt)) purchase_ne,
sum(DECODE(pii.mrt_state_region_xref.region, 'NE', tot_ln_amt))/sum(DECODE(pii.mrt_state_region_xref.region, 'NE', tot_ln_qt)) refi_ne,
sum(DECODE(pii.mrt_state_region_xref.region, 'NE', tot_ln_amt))/sum(DECODE(pii.mrt_state_region_xref.region, 'NE', tot_ln_qt)) tot_mw,
sum(DECODE(pii.mrt_state_region_xref.region, 'NE', tot_ln_amt))/sum(DECODE(pii.mrt_state_region_xref.region, 'NE', tot_ln_qt)) purchase_mw,
sum(DECODE(pii.mrt_state_region_xref.region, 'NE', tot_ln_amt))/sum(DECODE(pii.mrt_state_region_xref.region, 'NE', tot_ln_qt)) refi_mw,
sum(DECODE(pii.mrt_state_region_xref.region, 'NE', tot_ln_amt))/sum(DECODE(pii.mrt_state_region_xref.region, 'NE', tot_ln_qt)) tot_sh,
sum(DECODE(pii.mrt_state_region_xref.region, 'NE', tot_ln_amt))/sum(DECODE(pii.mrt_state_region_xref.region, 'NE', tot_ln_qt)) purchase_sh,
sum(DECODE(pii.mrt_state_region_xref.region, 'NE', tot_ln_amt))/sum(DECODE(pii.mrt_state_region_xref.region, 'NE', tot_ln_qt)) refi_sh,
sum(DECODE(pii.mrt_state_region_xref.region, 'NE', tot_ln_amt))/sum(DECODE(pii.mrt_state_region_xref.region, 'NE', tot_ln_qt)) tot_wt,
sum(DECODE(pii.mrt_state_region_xref.region, 'NE', tot_ln_amt))/sum(DECODE(pii.mrt_state_region_xref.region, 'NE', tot_ln_qt)) purchase_wt,
sum(DECODE(pii.mrt_state_region_xref.region, 'NE', tot_ln_amt))/sum(DECODE(pii.mrt_state_region_xref.region, 'NE', tot_ln_qt)) refi_wt,
from pii.mrt_state_av_loan_am_mth, pii.mrt_state_region_xref
where pii.mrt_state_av_loan_am_mth.st_nm = pii.mrt__state_region_xref.st_nm

When I try this I get the error message: "936: ORA-00936: missing expression". I'm not sure where the problem is as I am not being given a reference to a line.

Can you see a problem with the syntax?

Thanks again

Michael

6. Moderator.
Join Date
Sep 2002
Location
UK
Posts
5,171
Lose the comma on the last column here:

refi_wt,

7. Registered User
Join Date
Feb 2004
Location
Charlotte, NC
Posts
79
Sorry, I hadn't looked at it closely enough, but I think I now have a problem with divide by zero errors. I need to lose records where the devisor is 0.

If I do this, then I can simplify each line to:

sum(DECODE(pii.mrt_state_region_xref.region, 'NE', pii.mrt_state_avg_loan_am_mth.tot_ln_am/pii.mrt_state_avg_loan_am_mth.tot_ln_qt)) purchase_wt

How could I rewrite each line to prevent 0's (The fields cannot contain NULL's, so I assume they contain 0's if there is no data)?

Thanks again for all your help

Michael

#### Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts
•