Home » Other » Training & Certification » Count,SUM, AVG of table data using PL/SQL
Count,SUM, AVG of table data using PL/SQL [message #275413] Fri, 19 October 2007 19:02 Go to next message
rvause
Messages: 3
Registered: October 2007
Junior Member
Hello, I'm very new to PL/SQL and I can use all the help I can get. I am being asked to create a table and load in data and then find the count, SUM, and AVG of that data. Count is fairly easy and I've managed to get that. It's the SUM and AVG I'm stumped on. Here is what I have so far, I'd be really grateful for help completing this script.

set serveroutput on
DECLARE
    TYPE capacity_table IS TABLE OF location.capacity%TYPE
    INDEX BY BINARY_INTEGER;
  current_cap_table Capacity_TABLE;
   CURSOR cap_cursor IS
    SELECT room, capacity
    FROM location;
  cap_cursor_row cap_cursor%ROWTYPE;
  current_cap_key number(3);
  
BEGIN
  --populate the table using the cursor
  FOR cap_cursor_row IN cap_cursor LOOP
     current_cap_table(cap_cursor_row.room) := cap_cursor_row.capacity;
  END LOOP;
  DBMS_OUTPUT.PUT_LINE('The total room count in the CURRENT_cap_TABLE is ' || 
  current_cap_table.COUNT );
  

End loop;

END;
/


Thanks in advance for any help.

Angel
Re: Count,SUM, AVG of table data using PL/SQL [message #275415 is a reply to message #275413] Fri, 19 October 2007 19:15 Go to previous messageGo to next message
BlackSwan
Messages: 25042
Registered: January 2009
Location: SoCal
Senior Member
SELECT room, count(capacity) CNT, sum(capacity) TOTAL, avg(capacity) AVERAGE FROM location group by room;
Re: Count,SUM, AVG of table data using PL/SQL [message #275417 is a reply to message #275415] Fri, 19 October 2007 19:43 Go to previous messageGo to next message
rvause
Messages: 3
Registered: October 2007
Junior Member
While I appreciate the quick response with the code that I already have I can't use that select statement. From what I've learned when you are using cursors you can't use the SUM, AVG, and COUNT functions. If I could it would me this a heck of a lot easier. Cool

But thanks.

Angel
Re: Count,SUM, AVG of table data using PL/SQL [message #275418 is a reply to message #275413] Fri, 19 October 2007 19:47 Go to previous messageGo to next message
BlackSwan
Messages: 25042
Registered: January 2009
Location: SoCal
Senior Member
> From what I've learned when you are using cursors you can't use the SUM, AVG, and COUNT functions.
Well, if you'd actually try it, I believe you'd discover that they work just fine.
I still fail to understand why you are using a sledgehammer (PL/SQL) to kill the fly & not just using straight SQL.

I suspect that the homework assignment states to use PL/SQL.
Re: Count,SUM, AVG of table data using PL/SQL [message #275419 is a reply to message #275418] Fri, 19 October 2007 20:33 Go to previous messageGo to next message
rvause
Messages: 3
Registered: October 2007
Junior Member
I'll gladly try it and see what happens. I only replied as I did because I attempted to use the sum function in a previous program and got the following error:

PLS-00204: function or pseudo-column 'SUM' may be used inside a SQL statement only

Could be how I was trying to use it.

I do appreciate the help and yes, it is a homework assignment, but unfortunately the class is an independent study so I'm essentially trying to teach myself this stuff with the aid of just my text, the notes and examples the prof. provided and the internet.

Angel
Re: Count,SUM, AVG of table data using PL/SQL [message #275420 is a reply to message #275413] Fri, 19 October 2007 21:05 Go to previous messageGo to next message
BlackSwan
Messages: 25042
Registered: January 2009
Location: SoCal
Senior Member
You'll find may fine coding examples at http://asktom.oracle.com
Re: Count,SUM, AVG of table data using PL/SQL [message #275422 is a reply to message #275420] Fri, 19 October 2007 23:26 Go to previous message
rleishman
Messages: 3724
Registered: October 2005
Location: Melbourne, Australia
Senior Member
Chapter 2 of the PL/SQL manual contains everything you need to get started.

The 11g manuals also contain a great tutorial.

Ross Leishman
Previous Topic: ORACLE APPS TECHNICAL TRAINING
Next Topic: Block structure
Goto Forum:
  


Current Time: Tue Dec 06 15:54:20 CST 2016

Total time taken to generate the page: 0.09575 seconds