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