How we get all_group_function's(Sum,avg,count,max and min

How we get all_group_function's(Sum,avg,count,max and min_value of a column(Sal) Using pl/sql anonymous block, with out using group function's.

You shouldn't use more than one select statement in entire the program.
Like cursor c is select * from <table_name>;
except this you can't use another select statement.
You can use no of variables as per requirement.

Question Posted / ashok

1 Answers
1990 Views
I also Faced
E-Mail Answers

How we get all_group_function's(Sum,avg,count,max and min_value of a column(Sal) Using pl/sql a..

Answer / shwetha n k

DECLARE
TYPE REF_CUR_TYPE IS REF CURSOR;
CUR_VAR REF_CUR_TYPE;
V_SAL NUMBER(20);
N NUMBER(20) := 0;
V_AVG NUMBER(20);
V_MAX NUMBER(15);
V_MIN NUMBER(15);
BEGIN
OPEN CUR_VAR FOR SELECT SAL FROM EMP ORDER BY SAL DESC;
LOOP
FETCH CUR_VAR INTO V_SAL;
EXIT WHEN CUR_VAR%NOTFOUND;
N := N+V_SAL;
----DBMS_OUTPUT.PUT_LINE(V_SAL);
---- V_MAX := LEAST(V_SAL);
----V_MAX := GREATEST(V_SAL);
IF CUR_VAR%ROWCOUNT = 1 THEN
V_MAX := V_SAL;
DBMS_OUTPUT.PUT_LINE ('MAXIMUM SAL OF ALL EMPLOYEES:'||V_MAX);
END IF;
IF CUR_VAR%ROWCOUNT = 15 THEN
V_MAX := V_SAL;
DBMS_OUTPUT.PUT_LINE ('MINIMUM SAL OF ALL EMPLOYEES:'||V_MAX);
END IF;
END LOOP;
DBMS_OUTPUT.PUT_LINE('EMPLOYEE TOTAL SALARY IS:'||N);
DBMS_OUTPUT.PUT_LINE('NO OF EMPLOYEES :' ||CUR_VAR%ROWCOUNT);
V_AVG := ROUND(N/CUR_VAR%ROWCOUNT,2);
DBMS_OUTPUT.PUT_LINE('AVERAGE SAL OF ALL EMPLOYEES:'||V_AVG);
---DBMS_OUTPUT.PUT_LINE('MINIMUM SAL OF ALL EMPLOYEES:'||V_MAX);
CLOSE CUR_VAR;
END;

Is This Answer Correct ?

0 Yes

0 No

Post New Answer