Aggregate GREATEST in T-SQL

Question

My SQL is rusty -- I have a simple requirement to calculate the sum of the greater of two column values:

CREATE TABLE [dbo].[Test]
(
    column1 int NOT NULL, 
    column2 int NOT NULL
);

insert into Test (column1, column2) values (2,3)
insert into Test (column1, column2) values (6,3)
insert into Test (column1, column2) values (4,6)
insert into Test (column1, column2) values (9,1)
insert into Test (column1, column2) values (5,8)

In the absence of the GREATEST function in SQL Server, I can get the larger of the two columns with this:

select column1, column2, (select max(c) 
                            from (select column1 as c
                                   union all
                                  select column2) as cs) Greatest
  from test

And I was hoping that I could simply sum them thus:

select sum((select max(c) 
              from (select column1 as c
                     union all
                    select column2) as cs))
  from test

But no dice:

Msg 130, Level 15, State 1, Line 7
Cannot perform an aggregate function on an expression containing an aggregate or a subquery.

Is this possible in T-SQL without resorting to a procedure/temp table?

UPDATE: Eran, thanks - I used this approach. My final expression is a little more complicated, however, and I'm wondering about performance in this case:

SUM(CASE WHEN ABS(column1 * column2) > ABS(column3 * column4)
         THEN column5 * ABS(column1 * column2) * column6
         ELSE column5 * ABS(column3 * column4) * column6 END)

Answer 1

Try this... Its not the best performing option, but should work.

SELECT
  'LargerValue' = CASE 
                   WHEN SUM(c1) >= SUM(c2) THEN SUM(c1)
                   ELSE SUM(c2)
                  END
FROM Test

Answer 2

Try this:

 SELECT SUM(CASE WHEN column1 > column2 
                 THEN column1 
                 ELSE column2 END) 
 FROM test

Answer 3

SELECT
    SUM(MaximumValue)
FROM (
    SELECT 
     CASE WHEN column1 > column2
     THEN
      column1
     ELSE
      column2
     END AS MaximumValue
    FROM
     Test
) A

Answer 4

FYI, the more complicated case should be fine, so long as all of those columns are part of the same table. It's still looking up the same number of rows, so performance should be very similar to the simpler case (as SQL Server performance is usually IO bound).

Answer 5

--Devang Shah -- how to find max from single row data -- eg (empid , data1,data2,data3 ) select emplid , max(tmp.a) from (select emplid,date1 from table union select emplid,date2 from table union select emplid,date3 from table ) tmp , table where tmp.emplid = table.emplid

Answer 6

select sum(id) from ( select (select max(c) from (select column1 as c union all select column2) as cs) id from test )