CODEWITHSUNDEEP
sqldatabasegroup-byaggregate-functions
Javi
Javi

Reputation: 19769

Sum rows and columns in sql

I need to sum some rows applying on each row a formula and after all sum in a last column all the results. I'd like that in the total column I didn't have to especify the previous formulas but use the name of the column, I mean something like the following code (which doesn't work because total_1, total_2 and total_3 are not recognized)

SELECT 
    t.id, 
    t.dpt, 
    sum(t1.coefficient * t1.value) AS total_1,
    sum(t2.coefficient * t2.value) AS total_2,
    sum(t3.coefficient * t3.value) AS total_3,
    (total_1 + total_2 + total_3) AS total -- Not recognized
FROM my table t
JOIN table1 t1 ON...
JOIN table2 t2 ON...
JOIN table3 t3 ON...
group by t.id, t.dpt

I know I could do something like thefollowing, but it emplies some redundancy in the definitions of the sums.

SELECT 
    t.id, 
    t.dpt, 
    sum(t1.coefficient * t1.value) AS total_1,
    sum(t2.coefficient * t2.value) AS total_2,
    sum(t3.coefficient * t3.value) AS total_3,
    sum(t1.coefficient * t1.value + t2.coefficient * t2.value + t3.coefficient * t3.value) AS total
FROM my table t
JOIN table1 t1 ON...
JOIN table2 t2 ON...
JOIN table3 t3 ON...
group by t.id, t.dpt

Is there any efficient query which can be helpful to avoid redefining the columns sums in the total column?

Thanks

Upvotes: 0

Views: 7720

Answers (2)

Michał Powaga
Michał Powaga

Reputation: 23173

The simplest way I think of is:

SELECT *, (total_1 + total_2 + total_3) AS total FROM (
    SELECT 
        t.id, 
        t.dpt, 
        sum(t1.coefficient * t1.value) AS total_1,
        sum(t2.coefficient * t2.value) AS total_2,
        sum(t3.coefficient * t3.value) AS total_3
    FROM my table t
    JOIN table1 t1 ON...
    JOIN table2 t2 ON...
    JOIN table3 t3 ON...
    GROUP BY t.id, t.dpt
) t

Added:

I've tested queries like bellow (I know that they differ, but you should test in on your own it's the best way to spot the difference). Before each run I've executed DBCC DROPCLEANBUFFERS and DBCC FREEPROCCACHE. 2nd run were executed after few minutes and the difference is due to the server load probably.

SET STATISTICS TIME ON

DBCC DROPCLEANBUFFERS
DBCC FREEPROCCACHE
go

select t1.col1, sum(i.col2) as s, sum(i.col2) * 4 as smult
from tab1 t1
join tab2 t2 on t1.col1 = t2.col1
group by t1.col1

DBCC DROPCLEANBUFFERS
DBCC FREEPROCCACHE
go

select *, s * 4 as smult from (
    select t1.col1, sum(i.col2) as s
    from tab1 t1
    join tab2 t2 on t1.col1 = t2.col1
    group by t1.col1
) t

1st query 1st run:

SQL Server parse and compile time: 
   CPU time = 0 ms, elapsed time = 516 ms.
SQL Server Execution Times:
   CPU time = 63 ms,  elapsed time = 3958 ms.

1st query 2nd run:

SQL Server parse and compile time: 
   CPU time = 0 ms, elapsed time = 374 ms.
SQL Server Execution Times:
   CPU time = 31 ms,  elapsed time = 4152 ms.

2nd query 1st run:

SQL Server parse and compile time: 
   CPU time = 0 ms, elapsed time = 513 ms.
SQL Server Execution Times:
   CPU time = 94 ms,  elapsed time = 3445 ms.

2nd query 2nd run:

SQL Server parse and compile time: 
   CPU time = 16 ms, elapsed time = 363 ms.
SQL Server Execution Times:
   CPU time = 125 ms,  elapsed time = 3146 ms.

Upvotes: 3

ivan
ivan

Reputation: 430

The most efficient way (at least in Oracle) will be to just:

SELECT 
    t.id, 
    t.dpt, 
    sum(t1.coefficient * t1.value) AS total_1,
    sum(t2.coefficient * t2.value) AS total_2,
    sum(t3.coefficient * t3.value) AS total_3,
    sum(t1.coefficient * t1.value) +  sum(t2.coefficient * t2.value) + sum(t3.coefficient * t3.value) AS total
FROM my table t
JOIN table1 t1 ON...
JOIN table2 t2 ON...
JOIN table3 t3 ON...
group by t.id, t.dpt

Oracle is smart enough to recognize that it has the sums already computed and does not change the execution plan, so no performance penalty.

You can even do it in the way you suggested: sum(t1.coefficient * t1.value + t2.coefficient * t2.value + t3.coefficient * t3.value). It will be all the same as performance I think.

The redundancy you talk about is eliminated by the smart optimizer.

Upvotes: 0

Related Questions