I need to select
Result must be:
19 | English
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.
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 ) )
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
SELECT TOP 1 is a comparatively minor CPU operation.
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
then it would be a be cleaner (IMHO)
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)
Besides an aggregate,
MAX() is a scalar function in DB2, equivalent of the standard
GREATEST(). See DB2 docs, scalar
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.