oracle sum by column without using union

Question

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

can you please elaborate more about the question? what does total_except_storage_30 mean, why in the first apperance the valus is 1505 and in the 2nd one it is 1251? Also, in the result - for storage_id=2, the amount is 61, why is that? — planben, Aug 27 '20 at 05:32
"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. and the second for product_id 1001. — , Aug 27 '20 at 05:36
you can use a simple group by + where to achieve the accumulative sum: select product_id, sum(amount) from tab where storage_id <> 30 group by product_id And there are multiple ways to avoid union (although if you could elaborate why you should avoid - maybe we can find another solution) - http://www.dba-oracle.com/t_sql_alternative_to_union_clause.htm https://stackoverflow.com/questions/49535872/alternative-to-union-clause — planben, Aug 27 '20 at 05:46

score 2 · Accepted Answer · answered Aug 27 '20 at 05:50

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

Thanks. But I can't have the result as a column. I want it as a row. I have been asked to do so. — , Aug 27 '20 at 05:55
look again at my example. There are 14 rows in the EMP table, and yet I get 17 rows back as the result. You are getting the source rows *and* 3 additional subtotal rows — Connor McDonald, Aug 27 '20 at 05:59

oracle sum by column without using union

1 Answers1