Sum multiple columns from tab1 * with value from tab2 querry

All posts relating to Oracle PL/SQL development.

Moderator: Tim...

Sum multiple columns from tab1 * with value from tab2 querry

Postby sellyh19 » Tue Feb 05, 2013 12:53 pm

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!!
sellyh19
Senior Member
 
Posts: 67
Joined: Wed Mar 02, 2011 2:24 pm

Re: Sum multiple columns from tab1 * with value from tab2 qu

Postby Tim... » Tue Feb 05, 2013 1:57 pm

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...
Tim...
Oracle ACE Director
Oracle ACE of the Year 2006 - Oracle Magazine Editors Choice Awards
OakTable Member
OCP DBA 7.3, 8, 8i, 9i, 10g, 11g
OCP Advanced PL/SQL Developer
Oracle Database: SQL Certified Expert
My website: http://www.oracle-base.com
My blog: http://www.oracle-base.com/blog
Tim...
Site Admin
 
Posts: 17970
Joined: Mon Nov 01, 2004 5:56 pm
Location: England, UK

Re: Sum multiple columns from tab1 * with value from tab2 qu

Postby sellyh19 » Wed Feb 06, 2013 6:28 am

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!!
sellyh19
Senior Member
 
Posts: 67
Joined: Wed Mar 02, 2011 2:24 pm

Re: Sum multiple columns from tab1 * with value from tab2 qu

Postby Tim... » Wed Feb 06, 2013 7:41 am

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...
Tim...
Oracle ACE Director
Oracle ACE of the Year 2006 - Oracle Magazine Editors Choice Awards
OakTable Member
OCP DBA 7.3, 8, 8i, 9i, 10g, 11g
OCP Advanced PL/SQL Developer
Oracle Database: SQL Certified Expert
My website: http://www.oracle-base.com
My blog: http://www.oracle-base.com/blog
Tim...
Site Admin
 
Posts: 17970
Joined: Mon Nov 01, 2004 5:56 pm
Location: England, UK

Re: Sum multiple columns from tab1 * with value from tab2 qu

Postby sellyh19 » Wed Feb 06, 2013 12:20 pm

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.
sellyh19
Senior Member
 
Posts: 67
Joined: Wed Mar 02, 2011 2:24 pm

Re: Sum multiple columns from tab1 * with value from tab2 qu

Postby Tim... » Wed Feb 06, 2013 1:53 pm

LOL.

No problem. :)

Cheers

Tim...
Tim...
Oracle ACE Director
Oracle ACE of the Year 2006 - Oracle Magazine Editors Choice Awards
OakTable Member
OCP DBA 7.3, 8, 8i, 9i, 10g, 11g
OCP Advanced PL/SQL Developer
Oracle Database: SQL Certified Expert
My website: http://www.oracle-base.com
My blog: http://www.oracle-base.com/blog
Tim...
Site Admin
 
Posts: 17970
Joined: Mon Nov 01, 2004 5:56 pm
Location: England, UK


Return to Oracle SQL and PL/SQL Development

Who is online

Users browsing this forum: No registered users and 2 guests