rohansr002 rohansr002 - 1 month ago 7
SQL Question

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

Answer

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
Comments