1. Get rid of all advertisements and get unlimited access to documents by upgrading to Premium Membership. Upgrade to Premium Now and also get a Premium Badge!

Interactive Report - sum of multiple columns from table1 X with table2 value

Discussion in 'Oracle Application Express (APEX)' started by sellyh19, Feb 5, 2013.

  1. sellyh19

    sellyh19 Active Member

    Messages:
    17
    Likes Received:
    0
    Trophy Points:
    80
    I have a challenge in oracle apex, were i want to sum multiple columns to give 3 extra rows namely points, Score, %score. There are more columns but chose only few for now.
    Below is an example structure of my data:

    Town | Sector | Outside| Inside |Available|Price
    Roy-----Formal----0----------0----------1------0
    Kobus --Formal----0 ---------0--------- 1------0
    Wika ---Formal----0----------0--------- 1------0
    Mevo----Formal----1----------1---------1------0
    Hoch----Formal----1----------1---------1-------1

    Points--------------2----------2----------5-------1
    Score-------------10---------10---------10------10
    %score-----------20---------20---------50------10

    Each column has a constant weighting (which serves as a factor and it can change depending on the areas) in this case the weighting for this areas are the ones in the first row for the sector Formal:

    Sector |Outside| Inside |Available|Price
    Formal----1----------1 ----------1-----1
    Informal--1----------0 ----------2-----1

    I tried using the aggregate sum function in apex but it wont work since i need the factor in the other table. Which is where my challenge started

    To compute the rows below the report:

    points = sum per column * weighting factor per column
    Score = sum of no of shops visited (in this case its 5) * weighting factor per column
    % score = points/Score * 100

    The report should display as described above. With the new computed rows below.

    I kindly ask anyone to assist me with this challenge as i tried searchin on the internet for solutions but havent come across any.

    Thanks a lot for your support in advance!!
     
  2. zargon

    zargon Community Moderator Forum Guru

    Messages:
    2,345
    Likes Received:
    347
    Trophy Points:
    1,430
    Location:
    Aurora, CO
    Here is one solution to the problem but it uses standard SQL*Plus statements (which should be usable in APEX):

    Code (SQL):
    SQL> CREATE TABLE real_estate(
      2          town            varchar2(40),
      3          sector          varchar2(12),
      4          outside         NUMBER,
      5          inside          NUMBER,
      6          available       NUMBER,
      7          price           NUMBER);
     
    TABLE created.
     
    SQL>
    SQL> CREATE TABLE real_estate_col_weights(
      2          sector          varchar2(12),
      3          outside_wt      NUMBER,
      4          inside_wt       NUMBER,
      5          available_wt    NUMBER,
      6          price_wt        NUMBER);
     
    TABLE created.
     
    SQL>
    SQL> INSERT ALL
      2  INTO real_estate
      3  VALUES('Roy','Formal',0,0,1,0)
      4  INTO real_estate
      5  VALUES('Kobus','Formal',0,0,1,0)
      6  INTO real_estate
      7  VALUES('Wika','Formal',0,0,1,0)
      8  INTO real_estate
      9  VALUES('Mevo','Formal',1,1,1,0)
     10  INTO real_estate
     11  VALUES('Hoch','Formal',1,1,1,1)
     12  INTO real_estate_col_weights
     13  VALUES('Formal',1,1,1,1)
     14  INTO real_estate_col_weights
     15  VALUES('Informal',1,0,2,1)
     16  SELECT * FROM dual;
     
    7 ROWS created.
     
    SQL>
    SQL> commit;
     
    Commit complete.
     
    SQL>
    SQL> SELECT rpad(town, 40, '-') ||
      2          rpad(sector, 12, '-') ||
      3          lpad(outside, 11, '-') ||
      4          lpad(inside, 11, '-') ||
      5          lpad(available, 11, '-') ||
      6          lpad(price, 11, '-')
      7  FROM real_estate;
     
    Roy-------------------------------------Formal----------------0----------0----------1----------0
    Kobus-----------------------------------Formal----------------0----------0----------1----------0
    Wika------------------------------------Formal----------------0----------0----------1----------0
    Mevo------------------------------------Formal----------------1----------1----------1----------0
    Hoch------------------------------------Formal----------------1----------1----------1----------1
     
    SQL>
    SQL> WITH points AS(
      2          SELECT sector,
      3                  SUM(outside) outside,
      4                  SUM(inside) inside,
      5                  SUM(available) available,
      6                  SUM(price) price
      7          FROM real_estate
      8          GROUP BY sector
      9  ),
     10  score AS(
     11          SELECT s.outside*w.outside_wt outscore,
     12                  s.inside*w.inside_wt inscore,
     13                  s.available*w.available_wt availscore,
     14                  s.price*w.price_wt pricescore,
     15                  s.sector
     16          FROM points s, real_estate_col_weights w
     17          WHERE w.sector = s.sector
     18  ),
     19  pct_score AS(
     20          SELECT (outscore/(outscore+inscore+availscore+pricescore))*100 outpct,
     21                 (inscore/(outscore+inscore+availscore+pricescore))*100 inpct,
     22                 (availscore/(outscore+inscore+availscore+pricescore))*100 availpct,
     23                 (pricescore/(outscore+inscore+availscore+pricescore))*100 pricepct,
     24                  sector
     25          FROM score
     26  )
     27  SELECT rpad('Points',52,'-')||lpad(p.outside,11,'-')||lpad(p.inside,11,'-')||lpad(p.available,11,'-')||lpad(p.price,11,'-'),
     28         rpad('Score',52,'-')||lpad(s.outscore,11,'-')||lpad(s.inscore,11,'-')||lpad(s.availscore,11,'-')||lpad(s.pricescore,11,'-'),
     29         rpad('%Score',52,'-')||lpad(pct.outpct,11,'-')||lpad(pct.inpct,11,'-')||lpad(pct.availpct,11,'-')||lpad(pct.pricepct,11,'-')
     30  FROM points p, score s, pct_score pct
     31  WHERE s.sector = p.sector
     32  AND pct.sector = s.sector;
     
    Points--------------------------------------------------------2----------2----------5----------1
    Score---------------------------------------------------------2----------2----------5----------1
    %Score-------------------------------------------------------20---------20---------50---------10

    SQL>
    SQL> SET echo off pagesize 24
     
    Town                                   |Sector     |Outside    |Inside    |Available |Price
    Roy-------------------------------------Formal----------------0----------0----------1----------0
    Kobus-----------------------------------Formal----------------0----------0----------1----------0
    Wika------------------------------------Formal----------------0----------0----------1----------0
    Mevo------------------------------------Formal----------------1----------1----------1----------0
    Hoch------------------------------------Formal----------------1----------1----------1----------1

    Points--------------------------------------------------------2----------2----------5----------1
    Score---------------------------------------------------------2----------2----------5----------1
    %Score-------------------------------------------------------20---------20---------50---------10
     
     
    sellyh19 likes this.
  3. sellyh19

    sellyh19 Active Member

    Messages:
    17
    Likes Received:
    0
    Trophy Points:
    80
    Good morning Zargon,

    Thank you very much for your prompt response.

    My appology for not formatting my tables correct the dash between the data are not ment to be there, its only coz i didnt know how to format the data into a table format.

    I tried your solution and i have some questions:

    I removed lpad in the queries since data should not have ('-').

    Code (SQL):
    SELECT town,sector,outside,inside, available, price
       FROM real_estate;

    TOWN    SECTOR  OUTSIDE INSIDE  AVAIL  PRICE
    Roy Formal  0   0   1   0
    Kobus   Formal  0   0   1   0
    Wika    Formal  0   0   1   0
    Mevo    Formal  1   1   1   0
    Hoch    Formal  1   1   1   1
     
    When i remove the lpad from the second querry its returning only one row instead of 3 rows, how can i achieve that?

    Code (SQL):
    WITH points AS(
       SELECT sector,
      SUM(outside) outside,
       SUM(inside) inside,
       SUM(available) available,
       SUM(price) price
       FROM real_estate
      GROUP BY sector
         ),
       score AS(
      SELECT s.outside*w.outside_wt outscore,
                       s.inside*w.inside_wt inscore,
                      s.available*w.available_wt availscore,
                      s.price*w.price_wt pricescore,
                       s.sector
      FROM points s, real_estate_col_weights w
     WHERE w.sector = s.sector
      ),
      pct_score AS(
     SELECT (outscore/(outscore+inscore+availscore+pricescore))*100 outpct,
     (inscore/(outscore+inscore+availscore+pricescore))*100 inpct,
     (availscore/(outscore+inscore+availscore+pricescore))*100 availpct,
     (pricescore/(outscore+inscore+availscore+pricescore))*100 pricepct,
                      sector
     FROM score
     )
     SELECT 'Points',p.outside,p.inside,p.available,p.price,
            'Score',s.outscore,s.inscore,s.availscore,s.pricescore,
             '%Score',pct.outpct,pct.inpct,pct.availpct,pct.pricepct
     FROM points p, score s, pct_score pct
     WHERE s.sector = p.sector
     AND pct.sector = s.sector;

     
    How to i combine both queries to give me one output as my requirement?

    Thank you very much for your assistance. I really appreciate!
     
  4. sellyh19

    sellyh19 Active Member

    Messages:
    17
    Likes Received:
    0
    Trophy Points:
    80
    Hi Zargon,

    Thank you very much for your assistance, i manage to get the solution and my desired output, i had to play around with my query.

    Thanks, very much appreciate your assistance!!!! :hurray:)