2

I need to select MAX() between SUM(Math), SUM(English), SUM(Physics)

table

Result must be:

19 | English

closed as unclear what you're asking by Tom V, mustaccio, RLF, Max Vernon, Andriy M Apr 14 '16 at 21:27

Please clarify your specific problem or add additional details to highlight exactly what you need. As it's currently written, it’s hard to tell exactly what you're asking. See the How to Ask page for help clarifying this question. If this question can be reworded to fit the rules in the help center, please edit the question.

  • 2
    What are you going to do if the person has two values that are maximum? – Jonathan Fite Apr 14 '16 at 18:30
  • 3
    Which DBMS are you using? – a_horse_with_no_name Apr 14 '16 at 18:50
2

It's never going to be pretty, but heres one attempt:

select gt, case gt when math then 'Math' 
                   when physics then 'Physics' 
                   when english then 'English' 
           end 
from (
    select greatest(math, physics, english) as gt
         , math, english, physics 
    from (
        select sum(math) as math
             , sum(english) as english
             , sum(physics) as physics 
        from scores
    )
)
  • I like this better than the UNPIVOT solution because it is not version dependent. Hopefully everyone is upgraded past 2005, but if you are still stuck in 2000 land... – Steve Mangiameli Apr 14 '16 at 18:53
  • 1
    @SteveMangiameli: I don't see how this is better than UNPIVOT. While UNPIVOT is not supported in 2000 or lower versions, GREATEST isn't supported in any version (if we are speaking about SQL Server). – Andriy M Apr 14 '16 at 21:31
2

If you are using SQL Server, this is a good scenario to use UNPIVOT. You can use write a SELECT to give you the single-row result of SUM values, then apply UNPIVOT to essentially convert that row into a column... and from there you just SELECT TOP 1...

Here's an example that gives you the exact results you're looking for:

create table #Scores
(
      name    nvarchar(255)
    , Math    int
    , English int
    , Physics int
)

insert #Scores (name, Math, English, Physics)
values 
      ('Pete',4,5,3)
    , ('Mark',5,5,4)
    , ('David',4,5,3)
    , ('Lisa',3,4,4);


SELECT TOP 1 sup.SubjectName, sup.Score
FROM 
    (SELECT  SUM(Math) AS Math, 
             SUM(English) AS English, 
             SUM(Physics) AS Physics 
     FROM #Scores) s
UNPIVOT
    (Score FOR SubjectName IN (Math, English, Physics)) sup
ORDER BY sup.Score DESC

A few other answers "manually unpivot" the results by performing a UNION ALL for individual SELECT...SUM() statements. However, in that scenario, you would see one table scan for each SELECT...SUM() (ie, one per column that you manually unpivot).

From a performance perspective, using the UNPIVOT syntax will scale much better than the UNION ALL approach. The UNION ALL approach will access the table once for each statement (in this case, three times). By getting the three SUM values in a single SELECT, SQL Server will perform a single table scan to get the three SUM values, then the UNPIVOT and SELECT TOP 1 is a comparatively minor CPU operation.

1

Not exactly pretty

select top (1)WITH TIES tt.* 
from ( select 'math'    as [subject], sum(math)    as [sum] from table 
       union all
       select 'english' as [subject], sum(english) as [sum] from table 
       union all
       select 'Physics' as [subject], sum(Physics) as [sum] from table 
     ) tt
order by tt.sum desc 

Victim of data design
If you had a table
userID
subjectID
score
then it would be a be cleaner (IMHO)

1

This works in DB2:

select  max(sum(cola), sum(colb))
from    table

As people don't think this works, here is an example you can run on your own db2 instance:

select max(sum(col1),sum(col2)) 
from (values(1,2),(4,3),(5,6)) as temp (col1,col2)

returns 11.

Besides an aggregate, MAX() is a scalar function in DB2, equivalent of the standard GREATEST(). See DB2 docs, scalar MAX().

  • 2
    This is will show the topmost total but won't show which subject(s) scored it. – Andriy M Apr 14 '16 at 21:27
  • @AndriyM, I agree, this does not return the subject as well as the max. – Andreas Apr 15 '16 at 18:36
0

Even less pretty

CREATE TABLE Scores
(
    Name NVARCHAR(100),
    Math INT,
    English INT,
    Physics INT
);

INSERT Scores
VALUES  ('Pete',4,5,4),
        ('Mark',5,5,4),
        ('David',4,5,3),
        ('Lisa',3,4,4);

;WITH cte
AS
(
  SELECT SUM(Math) AS Math,
         SUM(ENGLISH) AS English,
         SUM(Physics) AS Physics
  FROM Scores
)

SELECT CASE
         WHEN Math > English AND Math > Physics THEN CONVERT(NVARCHAR(5), Math) + ' | Math'
         WHEN English > Math AND English > Physics THEN CONVERT(NVARCHAR(5), English) + ' | English'
         WHEN Physics > English AND Physics > Math THEN CONVERT(NVARCHAR(5), Physics) + ' | Physics'
         ELSE 'Error'
       END AS HighestResult
FROM cte;

SQL Fiddle here.

Not the answer you're looking for? Browse other questions tagged or ask your own question.