CODEWITHSUNDEEP
sqloracle-database
Kishan
Kishan

Reputation: 362

Find sum of unique values based on another distinct column

I am new to Oracle. I work on Oracle Database 12c version.There are two datafile copies for one file#. I need to calculate the total size of backup datafile copies only for second set of file# without including the first copy.

Output of query shows two or more copies for one file#

If I sum up to find total size of the datafile, it displays the sum of both datafile copies.

SQL> select sum((datafile_blocks)*8/1024) from v$BACKUP_DATAFILE;

SUM((DATAFILE_BLOCKS)*8/1024)
-----------------------------
                    10738.375

I require only the total size of either first copy or second copy

SQL> select file#,count(*),sum((datafile_blocks)*8/1024) from v$BACKUP_DATAFILE group by file#;

     FILE#   COUNT(*) SUM((DATAFILE_BLOCKS)*8/1024)
---------- ---------- -----------------------------
         1          2                          1900
         6          2                      3588.625
         2          2                         32.25
         5          2                          2500
         4          2                           670
         8          2                         87.25
         3          2                          1780
         7          2                         32.25
         9          2                       108.125
        10          2                            20
         0          4                        19.875

11 rows selected.

If I group by with having clause, it displays 0 rows

SQL> select sum((datafile_blocks)*8/1024) from v$BACKUP_DATAFILE group by file# having count(file#)=1;

no rows selected

Upvotes: 0

Views: 108

Answers (2)

Dmitry Grekov
Dmitry Grekov

Reputation: 688

If i get it right, you need only the last statement for each file. Do you?

In this case I'd suggest smth like this:

with 
    t as (
        select file#, 
               datafile_blocks * 8 / 1024 sum_blk,
               checkpoint_time,
               row_number() over (
                         partition by file# 
                         order by checkpoint_time desc) desc_row_no 
          from  v$backup_datafile 
    )
select * 
  from t 
 where desc_row_no = 1

Upvotes: 1

Littlefoot
Littlefoot

Reputation: 143013

Are you sure you want to dismiss some file#s?

On my 18cXE, there are "duplicate" file# values, but - see datafile_blocks - they are different

SQL> select file#, datafile_blocks, block_size
  2  from v$backup_datafile
  3  order by file#;

     FILE# DATAFILE_BLOCKS BLOCK_SIZE
---------- --------------- ----------
         0             646      16384
         0             646      16384
         1          121600       8192  --> different DATAFILE_BLOCKS for
         1          106240       8192  --> the same FILE#
         2           25600       8192
         3           61440       8192
         3           65280       8192
         4           26880       8192
         4            8320       8192
         5           51200       8192
         7             640       8192
         7             640       8192
         8            1288       8192

13 rows selected.

Anyway, to answer your question: how about dividing sum you already calculated with value count(*) returns? Something like this (see line #5) (I used your query):

SQL> select file#,
  2         sum(datafile_blocks * 8 / 1024) sum_blk,
  3         count(*) cnt,
  4         --
  5         sum(datafile_blocks * 8 / 1024) / count(*) result
  6  from v$backup_datafile
  7  group by file#;

     FILE#    SUM_BLK        CNT     RESULT
---------- ---------- ---------- ----------
         1       1780          2        890
         7         10          2          5
         2        200          1        200
         8    10,0625          1    10,0625
         4        275          2      137,5
         5        400          1        400
         3        990          2        495
         0   10,09375          2   5,046875

8 rows selected.

SQL>

Upvotes: 1

Related Questions