Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
sharcfinz
Starting Member
4 Posts |
 Posted - 2010-08-25 : 16:14:49
|
| I've got a view that has calculated columns, and I need to add another column that gives a total sum of the values in each row.. .. so ColumnA + ColumnB + ColumnC etc... .Part of my query looks like this:SELECT 1 AS ResultOrder, 'TotalScreen' AS TotalName, SUM(CASE WHEN Site = '01' THEN CASE WHEN DateAsked = dateadd(ms, 0, CONVERT(varchar, GetDate(), 101)) THEN 1 ELSE 0 END ELSE 0 END) AS [Column A], SUM(CASE WHEN Site = '02' THEN CASE WHEN DateAsked= dateadd(ms, 0, CONVERT(varchar, GetDate(), 101)) THEN 1 ELSE 0 END ELSE 0 END) AS [Column B], SUM(CASE WHEN Site = '03' THEN CASE WHEN DateAsked= dateadd(ms, 0, CONVERT(varchar, GetDate(), 101)) THEN 1 ELSE 0 END ELSE 0 END) AS [Column C], '' AS 'Across Sites'In that last column that I called 'Across Sites', I want to sum up the values of ColumnA, ColumnB, and ColumnC. but how do I reference them? When I put ([ColumnA] + [ColumnB] + [ColumnC]) AS 'Across Sites', I get an error.Help please? |
|
|
webfred
Master Smack Fu Yak Hacker
8781 Posts |
Posted - 2010-08-25 : 18:33:25
|
You have to repeat the statements sum(...) + sum(...) + sum(...) as [Across Sites]or you can use your query as derived table and build your last column in the outer query.
No, you're never too old to Yak'n'Roll if you're too young to die. |
 |
|
|
sharcfinz
Starting Member
4 Posts |
Posted - 2010-08-26 : 08:58:35
|
| Thanks a lot. That did the trick :-) I guess I was thinking that I was just a special way to reference those column names but there wasn't. . . thanks again! |
|
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2010-08-26 : 11:05:36
|
there's a special way.but for that you need to make your query a derived table likeSELECT put the other columns here...,[ColumnA] + [ColumnB] + [ColumnC] AS [Across Sites]FROM(SELECT 1 AS ResultOrder, 'TotalScreen' AS TotalName, SUM(CASE WHEN Site = '01' THEN CASE WHEN DateAsked = dateadd(ms, 0, CONVERT(varchar, GetDate(), 101)) THEN 1 ELSE 0 END ELSE 0 END) AS [Column A], SUM(CASE WHEN Site = '02' THEN CASE WHEN DateAsked= dateadd(ms, 0, CONVERT(varchar, GetDate(), 101)) THEN 1 ELSE 0 END ELSE 0 END) AS [Column B], SUM(CASE WHEN Site = '03' THEN CASE WHEN DateAsked= dateadd(ms, 0, CONVERT(varchar, GetDate(), 101)) THEN 1 ELSE 0 END ELSE 0 END) AS [Column C]... )t also dont put '' across column aliases------------------------------------------------------------------------------------------------------SQL Server MVPhttp://visakhm.blogspot.com/ |
|
|
|
|
|
|
|
|
how to reference columns??