Summing Zero Rows in Oracle

Question

I have a dataset like this:

+---------------+-------+
| SAMPLE_NUMBER | SCORE |
+---------------+-------+
|             1 | 100   |
|             2 | 97    |
|             3 | 124   |
|             4 | 762   |
|             5 | 999   |
|             6 | 1200  |
|             7 | NULL  |
|             8 | NULL  |
|             9 | NULL  |
|            10 | NULL  |
+---------------+-------+

I want to be able to sum NULL strings, rather than showing them all. Therefore, ideally, I would like the above to look like this:

+---------------+-------+
| SAMPLE_NUMBER | SCORE |
+---------------+-------+
| 1             | 100   |
| 2             | 97    |
| 3             | 124   |
| 4             | 762   |
| 5             | 999   |
| 6             | 1200  |
| 7-10          | NULL  |
+---------------+-------+

Is there a way in Oracle to do this? Or is this what I will need to do after the request?

Answer 1

My guess is that this should be part of your presentation level, since you will need to set sample_number to a string (if it's a numeric type. An alternative to your requirements is to return the minimum and maximum sequential sample_number:

with t (SAMPLE_NUMBER, SCORE) as (
    values  (1, 100)
        ,   (2, 97)
        ,   (3, 124)
        ,   (4, 762)
        ,   (5, 999)
        ,   (6, 1200)
        ,   (7, NULL)
        ,   (8, NULL)
        ,   (9, NULL)
        ,   (10, NULL)
)
select min(sample_number), max(sample_number), grp, score
from (
    select SAMPLE_NUMBER, SCORE
       ,   row_number() over (order by SAMPLE_NUMBER)
       -   row_number() over (partition by SCORE
                              order by SAMPLE_NUMBER) as grp
    from t
) group by grp, score
order by grp;

1           2           GRP                  SCORE      
----------- ----------- -------------------- -----------
      1           1                    0         100
      2           2                    1          97
      3           3                    2         124
      4           4                    3         762
      5           5                    4         999
      6           6                    5        1200
      7          10                    6           -

db2, , , .

: , .

with t (SAMPLE_NUMBER, SCORE) as (
    values  (1, 100)
        ,   (2, 97)
        ,   (3, 97)
        ,   (4, 762)
        ,   (5, 999)
        ,   (6, 1200)
        ,   (7, NULL)
        ,   (8, NULL)
        ,   (9, NULL)
        ,   (10, NULL)
)
select min(sample_number), max(sample_number), grp, score
from (
    select SAMPLE_NUMBER, SCORE
       ,   row_number() over (order by SAMPLE_NUMBER)
       -   row_number() over (partition by SCORE
                              order by SAMPLE_NUMBER) as grp
    from t
) group by grp, score
         , case when score is not null then sample_number end
order by grp;

1           2           GRP                  SCORE      
----------- ----------- -------------------- -----------
      1           1                    0         100
      2           2                    1          97
      3           3                    1          97
      4           4                    3         762
      5           5                    4         999
      6           6                    5        1200
      7          10                    6           -

, max , min:

[...]
select min(sample_number)
     , nullif(max(sample_number), min(sample_number))
     , grp
     , score
from ...

1           2           GRP                  SCORE      
----------- ----------- -------------------- -----------
      1           -                    0         100
      2           -                    1          97
      3           -                    1          97
      4           -                    3         762
      5           -                    4         999
      6           -                    5        1200
      7          10                    6           -

Answer 2

. :

select (case when score is null then min(sample_number) || '-' || max(sample_number)
             else min(sample_number)
        end) as sample_number,
       score
from table t
group by score
order by min(id)

, group by score, . : , . , :

select (case when score is null then min(sample_number) || '-' || max(sample_number)
             else min(sample_number)
        end) as sample_number,
       score
from (select t.*,
             row_number() over (partition by score order by sample_number) as seqnum
      from table t
     ) t
group by score, (case when score is not null then seqnum end);

Answer 3

SELECT DISTINCT 
    DECODE(SCORE
          ,NULL
          ,(SELECT COUNT()+1 
            FROM TAB_NAME 
            WHERE SCORE IS NOT NULL)
    || '-'
    || (SELECT COUNT() 
        FROM TAB_NAME)
          ,SAMPLE_NUMBER) NUM
   , NVL(TO_CHAR(SCORE),'NULL') SCRE
FROM TAB_NAME
ORDER BY 1 ASC;