Results 1 to 7 of 7
  1. #1
    Join Date
    Feb 2004
    Location
    Charlotte, NC
    Posts
    79

    Question 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. #2
    Join Date
    Sep 2002
    Location
    UK
    Posts
    5,171
    Provided Answers: 1

    Re: Calculations in select statement

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

  3. #3
    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. #4
    Join Date
    Sep 2002
    Location
    UK
    Posts
    5,171
    Provided Answers: 1
    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. #5
    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. #6
    Join Date
    Sep 2002
    Location
    UK
    Posts
    5,171
    Provided Answers: 1
    Lose the comma on the last column here:

    refi_wt,

  7. #7
    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
  •