|
|
Re: SQL query for formatted data display [message #653943 is a reply to message #653941] |
Fri, 22 July 2016 13:40 |
Solomon Yakobson
Messages: 3275 Registered: January 2010 Location: Connecticut, USA
|
Senior Member |
|
|
Well, in SQL*Plus you could do something like:
SQL> break on deptno skip 1
SQL> compute sum label "Total Salary" of sal on deptno
SQL> column sal format 99999.99
SQL> column deptno format 999999999999
SQL> select deptno,
2 ename,
3 job,
4 sal
5 from emp
6 order by deptno
7 /
DEPTNO ENAME JOB SAL
------------- ---------- --------- ---------
10 CLARK MANAGER 2450.00
KING PRESIDENT 5000.00
MILLER CLERK 1300.00
************* ---------
Total Salary 8750.00
20 JONES MANAGER 2975.00
FORD ANALYST 3000.00
ADAMS CLERK 1100.00
SMITH CLERK 800.00
SCOTT ANALYST 3000.00
************* ---------
Total Salary 10875.00
30 WARD SALESMAN 1250.00
TURNER SALESMAN 1500.00
ALLEN SALESMAN 1600.00
JAMES CLERK 950.00
BLAKE MANAGER 2850.00
MARTIN SALESMAN 1250.00
************* ---------
Total Salary 9400.00
14 rows selected.
SQL>
Otherwise, use GROUP BY GROUPING SETS:
SELECT CASE GROUP_ID() + GROUPING(ENAME)
WHEN 1 THEN DEPTNO
END DEPTNO,
CASE
WHEN GROUPING(ENAME) = 0 THEN ENAME
WHEN GROUP_ID() = 1 THEN 'Total Salary'
END ENAME,
JOB,
CASE GROUP_ID() + GROUPING(ENAME)
WHEN 1 THEN NULL
ELSE TO_CHAR(SUM(SAL),'9999990.00')
END SAL
FROM EMP E
GROUP BY GROUPING SETS((DEPTNO),(DEPTNO),(DEPTNO,ENAME,JOB))
ORDER BY E.DEPTNO,
GROUP_ID(),
E.ENAME NULLS FIRST
/
DEPTNO ENAME JOB SAL
---------- ------------ --------- -----------
10
CLARK MANAGER 2450.00
KING PRESIDENT 5000.00
MILLER CLERK 1300.00
Total Salary 8750.00
20
ADAMS CLERK 1100.00
FORD ANALYST 3000.00
JONES MANAGER 2975.00
SCOTT ANALYST 3000.00
SMITH CLERK 800.00
Total Salary 10875.00
30
ALLEN SALESMAN 1600.00
BLAKE MANAGER 2850.00
JAMES CLERK 950.00
MARTIN SALESMAN 1250.00
TURNER SALESMAN 1500.00
WARD SALESMAN 1250.00
Total Salary 9400.00
20 rows selected.
SQL>
SY.
[Updated on: Fri, 22 July 2016 13:42] Report message to a moderator
|
|
|