OraFAQ Forum: SQL & PL/SQL » Displaying Averages

Home » SQL & PL/SQL » SQL & PL/SQL » Displaying Averages

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

Displaying Averages [message #10947]

Wed, 25 February 2004 05:34

Michael Hart
Messages: 4
Registered: February 2004

Junior Member

Hello I am trying to display averages for second and third year exam results, I am able to process the average for 2nd year results separately and am like wise able to process the average for 3rd yr results separate using the following two separate queries.

SELECT SUM(AVG(emark)) "AVG Mark" FROM module, result WHERE result.mcode = module.mcode AND module.mcode LIKE '__3%' GROUP BY result.mcode;

SELECT SUM(AVG(emark)) "AVG Mark" FROM module, result WHERE result.mcode = module.mcode AND module.mcode LIKE '__2%' GROUP BY result.mcode;

But I need to be able to perform the two separate queries in one. Any help would be appreciated thankyou.

Report message to a moderator

Re: Displaying Averages [message #10951 is a reply to message #10947]

Wed, 25 February 2004 07:01

Satish Shrikhande
Messages: 167
Registered: October 2001

Senior Member

Use UNION

SELECT SUM(AVG(emark)) "AVG Mark" FROM module, result WHERE result.mcode = module.mcode AND module.mcode LIKE '__3%' GROUP BY result.mcode

UNION

SELECT SUM(AVG(emark)) "AVG Mark" FROM module, result WHERE result.mcode = module.mcode AND module.mcode LIKE '__2%' GROUP BY result.mcode;

Satish Shrikhande
Memphis , TN , USA

Report message to a moderator

Re: Displaying Averages [message #10966 is a reply to message #10947]

Thu, 26 February 2004 02:35

Barbara Boehmer
Messages: 9090
Registered: November 2002
Location: California, USA

Senior Member

I am not sure if you want to display separate results in one query or combine the results mathematically in one query. I have demonstrated both below, using the same sum of the averages that you have used.

scott@ORA92> -- test data:
scott@ORA92> SELECT * FROM result
  2  /

MCODE
-----------
__3rd year1
__2nd year1
__3rd year2
__2nd year2

scott@ORA92> SELECT * FROM module
  2  /

     EMARK MCODE
---------- -----------
         4 __3rd year1
         3 __3rd year1
         2 __2nd year1
         3 __2nd year1
         4 __3rd year2
         3 __3rd year2
         2 __2nd year2
         3 __2nd year2

8 rows selected.

scott@ORA92> -- original queries:
scott@ORA92> SELECT SUM(AVG(emark)) "AVG Mark"
  2  FROM   module, result
  3  WHERE  result.mcode = module.mcode
  4  AND    module.mcode LIKE '__3%'
  5  GROUP  BY result.mcode
  6  /

  AVG Mark
----------
         7

scott@ORA92> SELECT SUM(AVG(emark)) "AVG Mark"
  2  FROM   module, result
  3  WHERE  result.mcode = module.mcode
  4  AND    module.mcode LIKE '__2%'
  5  GROUP  BY result.mcode
  6  /

  AVG Mark
----------
         5

scott@ORA92> -- separate results displayed from one query:
scott@ORA92> SELECT SUM(AVG(emark)) "AVG Mark", 'second year' "Year"
  2  FROM   module, result
  3  WHERE  result.mcode = module.mcode
  4  AND    module.mcode LIKE '__3%'
  5  GROUP  BY result.mcode
  6  UNION ALL
  7  SELECT SUM(AVG(emark)) "AVG Mark", 'third year' "Year"
  8  FROM   module, result
  9  WHERE  result.mcode = module.mcode
 10  AND    module.mcode LIKE '__2%'
 11  GROUP  BY result.mcode
 12  /

  AVG Mark Year
---------- -----------
         7 second year
         5 third year

scott@ORA92> -- results combined mathematically:
scott@ORA92> SELECT SUM(AVG(emark)) "AVG Mark"
  2  FROM   module, result
  3  WHERE  result.mcode = module.mcode
  4  AND    (module.mcode LIKE '__3%'
  5  	     OR module.mcode LIKE '__2%')
  6  GROUP  BY result.mcode
  7  /

  AVG Mark
----------
        12

Report message to a moderator

Previous Topic:	Merge ERRO
Next Topic:	Username as prompt

Goto Forum:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Fri Apr 26 05:11:01 CDT 2024