sum of multiple columns in ORACLE

Question

I have a table that has 97 columns, I want to sum 96 columns.

select sum(col1+col2+col3+.....+col96) 
from tableA where meter_id=x;

I do not want to give all 96 column names, what is the best way to do it? Regards, RR

score 9 · Answer 1 · answered Feb 07 '13 at 10:44

9

There is no way to avoid writing each column name. All you can do is curse the stupid data modeller and get busy with cut'n'paste.

answered Feb 07 '13 at 10:44

APC

144,005
19
170
281

score 5 · Answer 2 · answered Feb 07 '13 at 20:05

5

In the case where there are a significant number of columns, I would look at using the data dictionary tables to help create the query by using a query like the one below:

Select column_name || '+' as column_name_list
From user_tab_columns
Where table_name = 'TABLEA'
Order by column_id

It doesn't change the world but does simplify writing one query.

answered Feb 07 '13 at 20:05

Mike Meyers

2,885
1
20
26

OP can use `dbms_xmlgen.getxml('')`, then extract single value from `/ROWSET/ROW/sum` xpath. Anyway, I agree this is worth only with significantly high amount of columns. – Tomáš Záluský Mar 06 '20 at 10:15

score 1 · Answer 3 · answered Feb 07 '13 at 17:11

1

You could create a virtual column that adds up your 96 columns, something like:

alter table TABLEA add (MY_TOTAL_COL NUMBER GENERATED ALWAYS AS (col1+col2+col3...) VIRTUAL);

Then your query can simply do sum(my_total_col).

answered Feb 07 '13 at 17:11

GriffeyDog

8,186
3
22
34

score 1 · Answer 4 · answered Jul 12 '17 at 12:32

You might be best to sum the columns and then put the result in Excel to do the sum of sums. Otherwise this query should do what you need:

SELECT SUM(TOTAL_SUM) FROM (
  SELECT SUM(column1) AS TOTAL_SUM FROM your_table
  UNION
  SELECT SUM(column2) AS TOTAL_SUM FROM your_table
  UNION
  SELECT SUM(column3) AS TOTAL_SUM FROM your_table
);

score 0 · Answer 5 · edited Mar 06 '20 at 09:46

SELECT A.consol_key, 
       A.amt_lcy, 
       B.amt_lcy, 
       C.amt_lcy 
FROM   categ A, 
       spec B, 
       stmt C; 

SELECT Sum(total_sum) 
FROM   (SELECT Sum(amt_lcy) AS TOTAL_SUM 
        FROM   categ 
        UNION 
        SELECT Sum(amt_lcy) AS TOTAL_SUM 
        FROM   spec 
        UNION 
        SELECT Sum(amt_lcy) AS TOTAL_SUM 
        FROM   stmt) 
WHERE  table_id NOT IN (SELECT table_id 
                        FROM   categ 
                        WHERE  txn_code = 'COR' 
                               AND system_id <> 'AA');

score 0 · Answer 6 · answered Mar 06 '20 at 11:40

It could be possible:

Using Can an SQL procedure return a table? and the answer of Mike Meyers you could write a stored procedure using dynamic sql

 sumcolumns(columnfilter,tablename,whereclause)

and use it something like

select * 
  from table(sumcolumns('column_name <> ''col97''','tableA','meter_id=x'))

score 0 · Answer 7 · answered Mar 06 '20 at 14:38

Try using UNPIVOT as per example below (still need to specify the column list as others have noted):

with tableA as /* prototype tableA just for example */
(
select 1 meter_id, 101 col1, 10 col2, 20 col3, 30 col4, NULL col5, 101 col11, 10 col12, 20 col13, 30 col14, NULL col15, 101 col21, 10 col22, 20 col23, 30 col24, NULL col25  from dual union
select 2, 102, 40, NULL, 50, NULL, 102, 40, NULL, 50, NULL, 102, 40, NULL, 50, NULL  from dual union
select 3, 103, 60, 70, 80, 90, 103, 60, 70, 80, 90, 103, 60, 70, 80, 90  from dual union
select 4, 104, 100, NULL, NULL, NULL, 104, 100, NULL, NULL, NULL, 104, 100, NULL, NULL, NULL  from dual
)
, unpivoted_tableA as /* UNPIVOT tableA columns into rows */
(
select *
from tableA
unpivot include nulls 
(
col_value for col_ in 
 (COL1,COL2,COL3,COL4,COL5,COL11,COL12,COL13,COL14,COL15,COL21,COL22,COL23,COL24,COL25)
)
)
/* main query - Sum of all columns that were unpivoted to rows */
select meter_id, sum(col_value) 
from unpivoted_tableA
group by meter_id
;

sum of multiple columns in ORACLE

7 Answers7