Total Sum of Multiple Columns in Oracle SQL Statement by Unique Id

Calculating and adding the totals row in Oracle for multiple columns

You can use CTE and MATERIALIZE hint to fetch the desired output as following:

with cte as (SELECT /*MATERIALIZE*/ <your query after select>)
select c.*
  from cte c
union all
select null, null, null, sum(col2), sum(col3), sum(col4)
  from cte;

The undocumented MATERIALIZE hint uses global temporary table so there will be no or little performance impact.

Cheers!!

How to SUM two fields within an SQL query

SUM is an aggregate function. It will calculate the total for each group. + is used for calculating two or more columns in a row.

Consider this example,

ID  VALUE1  VALUE2
===================
1   1       2
1   2       2
2   3       4
2   4       5

 

SELECT  ID, SUM(VALUE1), SUM(VALUE2)
FROM    tableName
GROUP   BY ID

will result

ID, SUM(VALUE1), SUM(VALUE2)
1   3           4
2   7           9

 

SELECT  ID, VALUE1 + VALUE2
FROM    TableName

will result

ID, VALUE1 + VALUE2
1   3
1   4
2   7
2   9

 

SELECT  ID, SUM(VALUE1 + VALUE2)
FROM    tableName
GROUP   BY ID

will result

ID, SUM(VALUE1 + VALUE2)
1   7
2   16

Counting DISTINCT over multiple columns

If you are trying to improve performance, you could try creating a persisted computed column on either a hash or concatenated value of the two columns.

Once it is persisted, provided the column is deterministic and you are using "sane" database settings, it can be indexed and / or statistics can be created on it.

I believe a distinct count of the computed column would be equivalent to your query.

Is there a way to select sum on one column based on other DISTINCT column, while grouping by third column(date) only

You can aggregate and then aggregate again:

select max(year), sum(money), count(*)
from (select distinct year, money, id
      from t
     ) t;

how to sum multiple rows with same id in SQL Server

Simple GROUP BY and SUM should work.

SELECT ID, NAME, SUM([NO]) 
FROM Your_TableName
GROUP BY ID, NAME;

---

Related Topics