Sum multiple columns from tab1 * with value from tab2 querry

[sellyh19]

Good day Tim,

I have been strugling lately to come up with a query that sum multiple columns and return extra 3 rows depending on what was selected.

For exapmle see my sample data below:

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 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.<br>
I kindly ask anyone to assist me with this challenge as i tried searching on the internet for solutions but havent come across any.

Thanks a lot for your support in advance!!

[Tim...]

Hi.

Please send a test case, including:

1) The CREATE TABLE statements (that you've tested) to create an example table or tables.
2) The INSERT statements (that you've tested) to populate the table(s).
3) The expected output. (You've already done this).

As I always say, if you want someone to help you, you've got to make it easy for them...

Cheers

Tim...

[sellyh19]

Good morning Tim,

Thanks for your prompt response.

Please see the below code for my tables structure and insert statements:

Code: Select all

 CREATE TABLE town_survey(
          town            varchar2(40),
          sector          varchar2(12),
          outside         NUMBER,
          inside          NUMBER,
          available       NUMBER,
          price           NUMBER);

Code: Select all

CREATE TABLE weights(
          sector          varchar2(12),
          outside_wt      NUMBER,
          inside_wt       NUMBER,
          available_wt    NUMBER,
          price_wt        NUMBER);

Code: Select all

INSERT ALL
INTO town_survey
 VALUES('Roy','Formal',0,0,1,0)
 INTO town_survey
 VALUES('Kobus','Formal',0,0,1,0)
 INTO town_survey
 VALUES('Wika','Formal',0,0,1,0)
 INTO town_survey
 VALUES('Mevo','Formal',1,1,1,0)
 INTO town_survey
 VALUES('Hoch','Formal',1,1,1,1)
 INTO weights
 VALUES('Formal',1,1,1,1)
 INTO weights
 VALUES('Informal',1,0,2,1);
commit;

Thanks for your support in advance!!

[Tim...]

Hi.

I'm being pedantic now, but why would you post these scripts that I still have to amend? I can not run them as is, because of the line numbers. Please post *working* code! I've corrected the scripts and will look at it later, after work.

Cheers

Tim...

[sellyh19]

Hi Tim,

Thanks very much for always assisting me.
Theres no need to look into this anymore, i found the solution.

Thanks alot, really appreciate it.

[Tim...]

LOL.

No problem.

Cheers

Tim...