StackOverflow2013

Note that there are some explanatory texts on larger screens.

plurals

POAggregate GREATEST in T-SQL
text
Body
copied!<p>My SQL is rusty -- I have a simple requirement to calculate the sum of the greater of two column values:</p> <pre><code>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) </code></pre> <p>In the absence of the GREATEST function in SQL Server, I can get the larger of the two columns with this:</p> <pre><code>select column1, column2, (select max(c) from (select column1 as c union all select column2) as cs) Greatest from test </code></pre> <p>And I was hoping that I could simply sum them thus:</p> <pre><code>select sum((select max(c) from (select column1 as c union all select column2) as cs)) from test </code></pre> <p>But no dice:</p> <pre><code>Msg 130, Level 15, State 1, Line 7 Cannot perform an aggregate function on an expression containing an aggregate or a subquery. </code></pre> <p>Is this possible in T-SQL without resorting to a procedure/temp table?</p> <p>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:</p> <pre><code>SUM(CASE WHEN ABS(column1 * column2) > ABS(column3 * column4) THEN column5 * ABS(column1 * column2) * column6 ELSE column5 * ABS(column3 * column4) * column6 END) </code></pre>

Querying!

Guidance

An individual column

Larger individual text columns get their own page to allow for proper reading.

SQuiL has stopped working due to an internal error.

If you are curious you may find further information in the browser console, which is accessible through the devtools (F12).

Reload