SUM UP two columns and then find the find MAX value in SQL Server

Question

I am working with Microsoft SQL Server and want to find E_ID and E_Name where T1+T2 has the MAX value.

I have two steps to reach the necessary result:

1. Find the sum of two columns AS "total" in a table
2. Find the row that contains the maximum value from total

Table named "table1" looks like the following (T2 may contains NULL values):

E_ID	E_Name	T1	T2
1	Alice	55	50
2	Morgan	60	40
3	John	65
4	Monica	30	10
5	Jessica	25
6	Smith	20	5

Here is what I've tried:

SELECT 
    E_ID, E_Name, MAX(total) AS max_t
FROM 
    (SELECT  
         E_ID, E_Name, ISNULL(T1, 0) + ISNULL(T2, 0) AS total
     FROM    
         table1) AS Q1;

I get this error:

'Q1.E_ID' is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause.

I get the result only when I keep MAX(total) AS max_t in the SELECT part but I also want to have the columns E_ID and E_Name.

Answer 1

Try this - just sort by the Total column in a descending fashion, and take the first row in the result:

SELECT TOP (1)
    Q1.E_ID, Q1.E_Name, Q1.Total
FROM 
    (SELECT  
         E_ID, E_Name, ISNULL(T1, 0) + ISNULL(T2, 0) AS Total
     FROM    
         table1) AS Q1
ORDER BY
    Q1.Total DESC;