Oracle SQL: ROLLUP not summing correctly

Question

Hi guys,

Rollup seems to be working correcly to count the number of units, but not the number of trains. Any idea what could be causing that?

The output from the query looks like this. The sum of the Units column in yellow is 53 but the rollup is showing 51. The number of units adds up correctly though...

And here's the oracle SQL query...

 select t.year,
    t.week,
    decode(t.mine_id,NULL,'PF',t.mine_id) as mine_id,
    decode(t.product,Null,'LF',t.product) as product,
    decode(t.mine_id||'-'||t.product,'-','PF',t.mine_id||'-'||t.product) as code,
    count(distinct t.tpps_train_id) as trains,
    count(1) as units

from 

 (
     select trn.mine_code as mine_id,
            trn.train_tpps_id as tpps_train_id,      
            round((con.calibrated_weight_total - con.empty_weight_total),2) as tonnes 
     from  widsys.train trn
               INNER JOIN widsys.consist con
                   USING (train_record_id)

     where trn.direction = 'N'
           and (con.calibrated_weight_total-con.empty_weight_total) > 10
           and trn.num_cars > 10 
   and con.consist_no not like '_L%'
    ) w,

     (
      select to_char(td.datetime_act_comp_dump-7/24, 'IYYY') as year,
             to_char(td.datetime_act_comp_dump-7/24, 'IW') as week,
             td.mine_code as mine_id,
             td.train_id as tpps_train_id,
             pt.product_type_code as product
      from tpps.train_details td
           inner join tpps.ore_products op
           using (ore_product_key)
           inner join tpps.product_types pt
           using (product_type_key)
      where to_char(td.datetime_act_comp_dump-7/24, 'IYYY') = 2010
            and to_char(td.datetime_act_comp_dump-7/24, 'IW') = 12
      order by td.datetime_act_comp_dump asc
 ) t 
where w.mine_id = t.mine_id
   and w.tpps_train_id = t.tpps_train_id

  having t.product is not null or t.mine_id is null 
    group by 
           t.year,
          t.week,  
         rollup(
          t.mine_id,
          t.product)

Answer 1

I think it is the DISTINCT. You are getting the total number of DISTINCT values, not the sum of the count of distinct values for each record.

select nvl(owner,'-') owner, count(distinct object_type) c1, count(*) c2
from all_objects
where owner in ('MDSYS','CTXSYS')
group by rollup(owner)

gives

OWNER     C1    C2
CTXSYS     6    82
MDSYS     11   653
-         11   735