Results 1 to 8 of 8
  1. #1
    Join Date
    Dec 2007
    Posts
    5

    Unanswered: Totaling a column without the use of the SUM function

    Is it possible to total a column using simple SQL statements without using functions.

    I have an assignment that asks me to total a column (salary) and then find the average without using SUM or AVG function in my SQL statement

    Thanks in Advance

  2. #2
    Join Date
    Nov 2007
    Location
    Connecticut
    Posts
    40
    Assigment seems rather ludicrous but

    I'm sure u can figure out the AVG from this on your own

    [CODE]delcare
    aggreg_val number:=0;
    cursor get_vals_cursor is select column_name from table_name ;
    begin
    for get_vals in get_vals_cursor LOOP
    aggreg_val := aggreg_val + get_vals.column_name;
    end loop;
    dbms_output.put_line (aggreg_val);
    end;
    /
    [\CODE]

  3. #3
    Join Date
    Dec 2007
    Posts
    5
    Can this be done thru SQL command line or is this code used in another application?

  4. #4
    Join Date
    Nov 2007
    Location
    Connecticut
    Posts
    40
    ABove is just a code snippet -- it is an ad-hoc pl/sql block -- it will work from sql*plus command line -- but its preferable to store in a file like exec_prog.sql and then from command - line sql*plus type -- @exec_prog
    why type it over and over again when u can store in a file

  5. #5
    Join Date
    Dec 2007
    Posts
    5
    Is there any easier way?? My class did not discuss PL/SQL. It is a class on basic SQL concepts. Can this be done just using simple select statement, subqueries, etc. This is how I solved it using functions but the next part of the question is solve the same problem without functions

    SELECT Name, Salary
    FROM Employee
    WHERE Salary >
    (SELECT AVG (Salary)
    FROM Employee);


    Thanks in advance
    Last edited by DMKanz; 12-14-07 at 16:40.

  6. #6
    Join Date
    Jun 2004
    Location
    Liverpool, NY USA
    Posts
    2,509
    This can be done by using sqlplus reporting.

    Code:
    compute sum of salary on report
    compute avg of salary on report
    
    SELECT Name, Salary
    		FROM Employee;
    Bill
    You do not need a parachute to skydive. You only need a parachute to skydive twice.

  7. #7
    Join Date
    Dec 2007
    Posts
    5
    Thank you Beilstwh

  8. #8
    Join Date
    Dec 2007
    Location
    India
    Posts
    5

    May I help You

    Dear Candidate , you can do one thing that is you can write PL/SQL block using counter for the for loop while accessing each primary key of IDs for salary and use variable to add the salary and find average also by using variables .
    Regards ,
    Experts.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •