CODEWITHSUNDEEP
sql-server
Surya sasidhar
Surya sasidhar

Reputation: 30323

How to sum the columns of the temporary table?

In SQL Server, I am inserting a row in a temporary table, and I am trying to sum the total of the columns at the end of the table. How can I do that?

In my temp tables, columns are dynamically created. How can I sum the columns now?

This is my query

 select * from #TempTable 
 union all 
 select SUM(2008),SUM(2009),SUM(2010),SUM(2011) from #TempTable

but it is giving an error:

invalid column name 2008,2009,2010,2011

Upvotes: 0

Views: 4152

Answers (3)

Martin Smith
Martin Smith

Reputation: 453658

So far as I can gather you have columns called 2008, 2009, 2010, 2011 and you are getting an error when trying to reference them?

You need to delimit them as they do not meet the standard rules for object identifiers.

SELECT SUM([2008]),
       SUM([2009]),
       SUM([2010]),
       SUM([2011])
FROM   #TempTable

Upvotes: 1

deroby
deroby

Reputation: 6002

When doing a UNION ALL, the most important thing to know is that all the queries must return the same number of columns. Also keep in mind that the columns are union-ed by their location in the result-set and not by their name!

So :

SELECT colA, colB, colC FROM ...
UNION ALL
SELECT colA FROM ...

will return in an error as the number of columns does not match.

SELECT colA, colB, colC FROM ...
UNION ALL
SELECT colB, colC, ColA FROM ...

MIGHT work but it will put everything from colB from the second query into the first column together with the data found in colA from the first query.

On top of that you should make sure that matching columns should be of the same type. You can't go mixing dates with strings for instance. The server will try to convert everything to the types as found in the top query, but if the (implicit) conversion fails, nothing is returned at all. (well, an error is returned I guess =)

Without us knowing what your #temp-table looks like (don't use SELECT * but rather use SELECT colA, colB, colC) we can't really help you out.

Going by the returned error, I'm not even sure your problem is with the UNION; it seems you're trying to conditionally SUM() some values based on the year they are respective too... I'm guessing there is some kind of year or date column in the temp-table to determine this ?!

Upvotes: 1

aF.
aF.

Reputation: 66727

Do it like this:

 select * from #TempTable 
 union all 
 select sum(ColumnName_from_#TempTable) from #TempTable

(Change the ColumnName_from_#TempTable)

Upvotes: 0

Related Questions