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:

Find the sum of two columns AS "total" in a table
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.

Why not use TOP?

Thom A
– Thom A ♦

2022-04-12 13:58:43 +00:00
Commented Apr 12, 2022 at 13:58 — Thom A
– Thom A ♦, Commented Apr 12, 2022 at 13:58

marc_s · Accepted Answer · 2022-04-12 14:01:18Z

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;

answered Apr 12, 2022 at 14:01

marc_s

760k186 gold badges1.4k silver badges1.5k bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Ankitha Gowda · Accepted Answer · 2022-04-12 14:17:02Z

0

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

answered Apr 12, 2022 at 14:17

Ankitha Gowda

1571 gold badge2 silver badges7 bronze badges

1 Comment

complog Over a year ago

It would again give an error with the same reason as mine: "Table1.T1" and "Table1.T2" are invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause."

Teja Tejaswi · Accepted Answer · 2022-04-12 14:26:38Z

0

SELECT

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

FROM table1

ORDER BY Total DESC;

answered Apr 12, 2022 at 14:26

Teja Tejaswi

11 bronze badge

Comments

Mark Melgo · Accepted Answer · 2022-04-13 16:13:52Z

0

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

edited Apr 13, 2022 at 16:13

Mark Melgo

1,4781 gold badge15 silver badges30 bronze badges

answered Apr 12, 2022 at 19:21

Dângelo

11 bronze badge

Comments

lemon · Accepted Answer · 2022-07-31 13:59:47Z

0

You can do it without subqueries, by ordering purposefully your data:

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

Check the demo here.

edited Jul 31, 2022 at 13:59

answered Apr 12, 2022 at 14:11

lemon

15.6k6 gold badges23 silver badges42 bronze badges

3 Comments

complog Over a year ago

Thanks! It also works. Is there a way to compare both queries regarding the complexity/running time?

lemon Over a year ago

Every time you use subqueries/joins you're adding a layer of complexity, as long as these two operations are the most expensive ones in sql (they're essentially the same join operation dressed with two different clothes). @complog

complog Over a year ago

I see! Thank you. Btw, you need to add "DESC" for the answer (warning just in case someone else want to have the solution)

Collectives™ on Stack Overflow

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

5 Answers 5

Comments

1 Comment

Comments

Comments

3 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

5 Answers 5

Comments

1 Comment

Comments

Comments

3 Comments

Your Answer

Sign up or log in

Post as a guest

Related