|
|
Re: How to generate Query aggregation in oracle [message #656637 is a reply to message #656636] |
Thu, 13 October 2016 00:31 |
|
Michel Cadot
Messages: 68665 Registered: March 2007 Location: Nanterre, France, http://...
|
Senior Member Account Moderator |
|
|
For all versions and using the standard EMP table:
SQL> select deptno, job from emp order by 1, 2;
DEPTNO JOB
---------- ---------
10 CLERK
10 MANAGER
10 PRESIDENT
20 ANALYST
20 ANALYST
20 CLERK
20 CLERK
20 MANAGER
30 CLERK
30 MANAGER
30 SALESMAN
30 SALESMAN
30 SALESMAN
30 SALESMAN
14 rows selected.
SQL> select deptno,
2 count(decode(job,'CLERK',job)) clerk,
3 count(decode(job,'SALESMAN',job)) salesman,
4 count(decode(job,'MANAGER',job)) manager,
5 count(decode(job,'PRESIDENT',job)) president
6 from emp
7 group by deptno
8 order by deptno
9 /
DEPTNO CLERK SALESMAN MANAGER PRESIDENT
---------- ---------- ---------- ---------- ----------
10 1 0 1 1
20 2 0 1 0
30 1 4 1 0
3 rows selected.
Starting with version 11g:
SQL> select *
2 from (select deptno, job from emp)
3 pivot(count(job)
4 for job in ('CLERK' clerk,
5 'SALESMAN' salesman,
6 'MANAGER' manager,
7 'PRESIDENT' president))
8 order by deptno
9 /
DEPTNO CLERK SALESMAN MANAGER PRESIDENT
---------- ---------- ---------- ---------- ----------
10 1 0 1 1
20 2 0 1 0
30 1 4 1 0
3 rows selected.
|
|
|