rohansr002 rohansr002 - 1 year ago 87
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 Source

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
Recommended from our users: Dynamic Network Monitoring from WhatsUp Gold from IPSwitch. Free Download