Sum rows and columns in sql

Question

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 JavaScript questions and answers, JavaScript questions pdf, JavaScript question bank, JavaScript questions and answers pdf, mcq on JavaScript pdf, JavaScript questions and solutions, JavaScript mcq Test , Interview JavaScript questions, JavaScript Questions for Interview, JavaScript MCQ (Multiple Choice Questions)

Answer 1

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.