Oracle Magazine, September/October 2018
Database Developer and DBA ETL ORACLE MAGAZINE SEPTEMBER OCTOBER 2018 93 Listing 7 Part 1 finding the total SQL SELECT dname SUM sal dept_ sal 2 FROM emp e dept d 3 WHERE e deptno d deptno 4 GROUP BY dname Using a WITH statement I can now assume the existence of the Listing 7 query as if it were a table in the database Given a virtual table containing that data dept_ salaries I can then construct a query to handle the second part of the requirement get the average of these totals as shown in Listing 8 Listing 8 Part 2 getting the average SQL WITH dept_ salaries AS 2 SELECT dname SUM sal dept_ sal 3 FROM emp e dept d 4 WHERE e deptno d deptno 5 GROUP BY dname 6 SELECT AVG dept_ sal avsal 7 FROM dept_ salaries The final part of the requirement
You must have JavaScript enabled to view digital editions.