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

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.

sqlsql-serverdatabasesummax


Solution 1:[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;

Solution 2:[2]

You can use the query:

SELECT top 1 E_ID, E_Name, (T1+T2) as Total 
FROM Table1 
GROUP BY E_ID,E_Name 
ORDER BY Total desc

enter image description here

Solution 3:[3]

You don't need subqueries:

SELECT 
    TOP 1
    E_Name
FROM 
    tab
ORDER BY 
    COALESCE(T1,0)+COALESCE(T2,0) DESC

Fiddle here: https://dbfiddle.uk/?rdbms=sqlserver_2019l&fiddle=0cca8f7ca28dc45dd1ece505f9176bdc.

Solution 4:[4]

SELECT

TOP (1) , E_ID, E_Name, ISNULL(T1, 0) + ISNULL(T2,0) AS Total

FROM table1

ORDER BY Total DESC;

Solution 5:[5]

If you wanna see all the records you just need to apply the GROUP BY clause.

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
GROUP BY
    E_ID
  , E_Name;

If you want to see only the MAX value in the dataset you just need to apply the TOP 1 (for one record in the result), then sum the T1 and T2 as total and then apply the ORDER BY DESC;

SELECT TOP 1
    E_ID
  , E_Name
  , (T1 + T2) AS total
FROM 
    table1
ORDER BY
    total DESC

Sources

This article follows the attribution requirements of Stack Overflow and is licensed under CC BY-SA 3.0.

Source: Stack Overflow

Solution Source
Solution 1 marc_s
Solution 2 Ankitha J
Solution 3
Solution 4 Teja Tejaswi
Solution 5 Mark Melgo

Related Questions