rohansr002 - 5 months ago 21

SQL Question

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

`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
```