There's a bit of ambiguity in your second question, but here's some things to get you started
SQL> select job, count(*)
2 from scott.emp
3 group by job
4 order by 1;
JOB COUNT(*)
--------- ----------
ANALYST 2
CLERK 4
MANAGER 3
PRESIDENT 1
SALESMAN 4
5 rows selected.
SQL>
SQL> select job, count(distinct deptno), count(*)
2 from scott.emp
3 group by job
4 order by 1;
JOB COUNT(DISTINCTDEPTNO) COUNT(*)
--------- --------------------- ----------
ANALYST 1 2
CLERK 3 4
MANAGER 3 3
PRESIDENT 1 1
SALESMAN 1 4
5 rows selected.
SQL>
SQL> select job, deptno, count(*)
2 from scott.emp
3 group by job,deptno
4 order by 1,2;
JOB DEPTNO COUNT(*)
--------- ---------- ----------
ANALYST 20 2
CLERK 10 1
CLERK 20 2
CLERK 30 1
MANAGER 10 1
MANAGER 20 1
MANAGER 30 1
PRESIDENT 10 1
SALESMAN 30 4
9 rows selected.
In order to put that in a PLSQL procedure, for each SQL you can do
set serverout on
begin
for i in ( "yoursql" )
loop
dbms_output.put_line( "attributes" );
end loop;
end;
/