oracle sum by column without using union

I have this table: (supply table: how many products in storages)

Storage_id           product_id        amount
1                       1000             55
1                       1005             1
...
29                      1000             3
29                      1421             21
29                      1566              0
30                      1259             921

I should write a query to have this result:

storage_id                  product_id         amount
1                             1000               55
2                             1000               61
... 
30                            1000               10
total_except_storage_30       1000              1505
1                             1001               1
2                             1001               50
...
30                            1001               56
total_except_storage_30       1001              1251
...

“Total_except_storage_30” has the total of every product in storages except storage number 30. For example first “total_except_storage_30” is for product_id 1000 in all storages except storage_id 30 and the second is for product_id 1001.

*** I am not allowed to use “Union”.

I tried to use full outer join but this did not work and the result is without “total_except_storage_30”:

Select t.Storage_id, t.product_id, t.amount
from myTable t full outer join 
(
    select 'total_except_storage_30' as storage_id, product_id, sum(amount)
    from myTable
    group by product_id
) total
on t.storage_id = total.storage_id

Answer

Something like this should do it

select 
  product,
  storage_id,
  sum(case when storage_id != 30 then sal end)
from scott.emp  
group by grouping sets (
  (storage_id,product),
  (product) 
)
order by product, storage_id;

Here’s an example of that using the standard EMP, DEPT

SQL> select
  2    deptno,
  3    empno,
  4    sum(sal)
  5  from scott.emp
  6  group by grouping sets (
  7    (empno,deptno),
  8    (deptno)
  9  )
 10  order by deptno, empno;

    DEPTNO      EMPNO   SUM(SAL)
---------- ---------- ----------
        10       7782       2450
        10       7839       5000
        10       7934       1300
        10                  8750
        20       7369        800
        20       7566       2975
        20       7788       3000
        20       7876       1100
        20       7902       3000
        20                 10875
        30       7499       1600
        30       7521       1250
        30       7654       1250
        30       7698       2850
        30       7844       1500
        30       7900        950
        30                  9400

17 rows selected.

You can see you get subtotals throughout