Multiple row Sums into a Total Column

Question

I have a temp table populated in a sproc that is similar to this:

Company   Col1      Col2     Col3    Total
Company1   4          3        2       9
Company2   1          0        3       4
Total      ?          ?        ?       ?

Is there a slick way to get the bottom total column populated with SUM of each row in one shot without having to do each column individually using sub-queries?

Answer 1

select sum(col1), sum(col2), sum(col3), sum(col1+col2+col3)
FROM CompanyTable

Answer 2

If the DBMS you use is MS SQL than be aware of how to reference the temporary table:

SELECT SUM(Col1) as TotalOfCol1,...
FROM #Company

Answer 3

If you are using MySQL, you can get the whole table in one query using the WITH ROLLUP feature:

SELECT Company, SUM(Col1), SUM(Col2), SUM(Col3), SUM(Col1 + Col2 + Col3)
FROM Table1
GROUP BY Company
WITH ROLLUP

Results:

'Company1'  4  3  2  9
'Company2'  1  0  3  4
''          5  3  5 13  <--- This extra row is the column totals