OraFAQ Forum: SQL & PL/SQL » calculating weighted averages

Home » SQL & PL/SQL » SQL & PL/SQL » calculating weighted averages

Show: Today's Messages :: Polls :: Message Navigator
E-mail to friend

calculating weighted averages [message #41989]

Thu, 20 February 2003 07:11

cubanallstar
Messages: 3
Registered: February 2003

Junior Member

Guys I really need your help on this one. Im designing a web page that has a log in, a student types his/her student number and password in and get a summary of there assignment and exam results. The thing I need to work out how to do is work out weighted averages. I already have the tables with the relevant info; MODULE_ID, ASSESSNO, TYPE_ID AND WEIGHTING.

Code I did think would work is

SELECT MODULE_ID, AVG(ASSESS_NO), COUNT(*) FROM WEIGHTING GROUP BY (WEIGHTING);

Doesnt work though am I on the right tracks?

Cubanallstar

Report message to a moderator

Re: calculating weighted averages [message #41994 is a reply to message #41989]

Thu, 20 February 2003 09:52

Art Metzer
Messages: 2480
Registered: December 2002

Senior Member

_quote:
----------------------------------------------------------------------

Guys I really need your help on this one. Im designing a web page that has a log in, a student types his/her student number and password in and get a summary of there assignment and exam results. The thing I need to work out how to do is work out weighted averages. I already have the tables with the relevant info; MODULE_ID, ASSESSNO, TYPE_ID AND WEIGHTING.
  
Code I did think would work is 
  
SELECT MODULE_ID, AVG(ASSESS_NO), COUNT(*) FROM WEIGHTING GROUP BY (WEIGHTING); 
  
Doesnt work though am I on the right tracks? 
  
Cubanallstar

----------------------------------------------------------------------
I'm not sure what module_id and assessno represent, C., so I made some assumptions here. If I'm off, you can probably adapt my solution to your particular scenario.

I took module_id to represent a student, assessno to represent a grade or a mark, and weighting to represent the relative weight of that grade or mark. I've set up the data so that one student's weightings all add up to 100, but that does not have to necessarily be true.

SQL> CREATE TABLE t1 (module_no NUMBER, assessno NUMBER, weighting NUMBER);
  
Table created.

Here, I inserted some data. To wit:

SQL> SELECT t1.module_no
  2  ,      t1.assessno
  3  ,      t1.weighting
  4  FROM   t1
  5  ORDER BY t1.module_no
  6  /
  
 MODULE_NO   ASSESSNO  WEIGHTING
---------- ---------- ----------
         1         50         30
         1         70         60
         1         90         10
         2         90         10
         2         80         20
         2         70         30
         2         60         40
         3         85        100
         4         70         50
         4         80         50
  
10 rows selected.
  
SQL> SELECT   t1.module_no
  2  ,        DECODE(SUM(NVL(t1.weighting,0))
  3           ,      0, TO_NUMBER(NULL)
  4           ,      (SUM(NVL(t1.assessno,0) 
  5                   *
  6                   NVL(t1.weighting,0)) / SUM(NVL(t1.weighting,0)))) weighted_grade
  7  FROM     t1
  8  GROUP BY t1.module_no
  9  /
  
 MODULE_NO WEIGHTED_GRADE
---------- --------------
         1             66
         2             70
         3             85
         4             75
  
SQL>

The key to calculating weighted averages is to add up the product of every exam result and its weighting, and then to divide this sum of products by the sum of all the weightings.

If not all the rows in your table have a value for weighting, you will get skewed results, which your version of the above SQL statement will have to account for.

Hope this helps, C.

Good luck,

A

Report message to a moderator

Re: calculating weighted averages [message #41997 is a reply to message #41989]

Thu, 20 February 2003 12:10

Scott Mackey
Messages: 46
Registered: February 2003

Member

Or if you know weighting is never going to be null or 0

SELECT   t1.module_no
,        SUM(NVL(t1.assessno,0) * t1.weighting) / SUM(t1.weighting) weighted_grade
FROM     t1
GROUP BY t1.module_no

Report message to a moderator

Previous Topic:	PL/SQL outputing HTML code
Next Topic:	Why These queries are giving different resuts

Goto Forum:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Sat Dec 20 05:48:03 CST 2025