Count,SUM, AVG of table data using PL/SQL [message #275413] |
Fri, 19 October 2007 19:02  |
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 #275419 is a reply to message #275418] |
Fri, 19 October 2007 20:33   |
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
|
|
|
|
|