CODEWITHSUNDEEP
sqloracle-databasepivot
rohansr002
rohansr002

Reputation: 107

How to generate Query aggregation in oracle?

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

Upvotes: 0

Views: 54

Answers (3)

Sanjay Radadiya
Sanjay Radadiya

Reputation: 1286

Try this using pivot query function in oracle

select * from(select nvl(col1,'Total') col1,nvl(col2,'Total') col2,count(1) cnt from mytable group by  CUBE(col1,col2))
pivot
(
     sum(cnt)
     for col2 IN
               (
                  'Fully'  AS "Fully",
                  'Partial'   AS "Partial",
                  'NoService' AS "NoService",
                  'Total' As "Total"
               )
)

here from above query i can get both column and row total.

use query as per your need.

Upvotes: 1

AT-2017
AT-2017

Reputation: 3149

In Oracle, you can do this using pivot or aggregate function with CASE:

SELECT COL1, 
  COUNT(CASE when COL2 = 'FULLY' THEN 1 ELSE 0 END) AS FULLY,
  COUNT(CASE when COL2 = 'PARTIAL' THEN 1 ELSE 0 END) AS PARTIAL
FROM TABLE_A A 
LEFT JOIN  TABLE_A B
  on B.COL1 = A.COL1
GROUP BY COL1

Upvotes: 1

Shushil Bohara
Shushil Bohara

Reputation: 5656

Try This: It's not a dynamic solution though it will provide the result what you expect. If you have fixed values in val2 then it's the best one otherwise you have to choose dynamic way.

SELECT col1,
    SUM(CASE WHEN col2 = 'Fully' THEN 1 ELSE 0 END) Fully,
    SUM(CASE WHEN col2 = 'Partial' THEN 1 ELSE 0 END) Partial,
    SUM(CASE WHEN col2 = 'NoService' THEN 1 ELSE 0 END) NoService,
    COUNT(col2) total
FROM yourTable
GROUP BY col1

Upvotes: 2

Related Questions