Home » SQL & PL/SQL » SQL & PL/SQL » How to generate Query aggregation in oracle (Oracle)
icon5.gif  How to generate Query aggregation in oracle [message #656631] Wed, 12 October 2016 23:38 Go to next message
rohansr002
Messages: 1
Registered: October 2016
Junior Member
I am working on some code, sample table shown below. Looking for aggregated output to some query

Sample Table

col1 col2
---- ----
val1 Fully
val1 Partial
val2 NoService
val2 Partial
val3 Fully
val3 NoService
val3 Fully
val3 Partial
val1 Fully
val2 NoService


Output expected

Col1 Fully Partial NoService Total
---- ----- ------- --------- -----
val1 2 1 0 3
val2 0 1 2 3
val3 2 1 1 4


Not sure how to do this in oracle? please some one help
Re: How to generate Query aggregation in oracle [message #656636 is a reply to message #656631] Thu, 13 October 2016 00:27 Go to previous messageGo to next message
Michel Cadot
Messages: 65317
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

Welcome to rhe forum.
Please read OraFAQ Forum Guide and How to use [code] tags and make your code easier to read.
Also always post your Oracle version, with 4 decimals, as solution depends on it, as for your current question.

With any SQL or PL/SQL question, please, Post a working Test case: create table (including all constraints) and insert statements along with the result you want with these data then we will be able work with your table and data. Explain with words and sentences the rules that lead to this result.

Re: How to generate Query aggregation in oracle [message #656637 is a reply to message #656636] Thu, 13 October 2016 00:31 Go to previous message
Michel Cadot
Messages: 65317
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.
Previous Topic: pl/sql table
Next Topic: ORA-01843: not a valid month and ORA-01847: day of month must be between 1 and last day of month
Goto Forum:
  


Current Time: Sun Jan 21 03:45:59 CST 2018

Total time taken to generate the page: 0.01557 seconds