CODEWITHSUNDEEP
mysql
sul
sul

Reputation: 13

How can I add mulitple columns and display maximum value?

In the following code, I am adding Fanbase, coach, TeamRanks, Organization and Kicker from Ranks table. It returns sum of each row as total. However, I am not sure, how I can display maximum value for total. I don't need the whole list of addition.

Select
  ID, 
  (Fanbase + Coach + TeamRanks + Organization + Kicker) as total,
  Fanbase, 
  Coach, 
  TeamRanks,
  Organization,
  Kicker
from Ranks;

This returns total for each column, however I need max. value of total.

Team     Fanbase    Coach    TeamRanks    Organization     Kicker


Chargers  80         60       29           18               10
Patriots  100       100       32           32               20
RAMS      80        90        30           25               23

Upvotes: 1

Views: 62

Answers (2)

FanoFN
FanoFN

Reputation: 7124

The quickest way you can do this (and without deriving away too much from your original query), you can use ORDER BY DESC and LIMIT.

-- Assuming that ID=teamname
SELECT ID FROM
(SELECT
  ID, 
  (Fanbase + Coach + TeamRanks + Organization + Kicker) AS total
FROM Ranks ORDER BY total DESC LIMIT 1) r;

UPDATE: I had one query in mind but I was afraid that it might not work in some MySQL version but just now I had time to try it and it seems to be working fine on MySQL 5.5, 5.6, 5.7 & 8.0. This query is much shorter and without using sub-query:

SELECT ID
FROM ranks 
ORDER BY  (Fanbase + Coach + TeamRanks + Organization + Kicker) DESC 
LIMIT 1;

And also I would like to point out that if one of the column contains NULL value for all of the teams; for example if all of the Kicker value is NULL, this query doesn't seem to work. I've check the mathematical formula, if (Fanbase + Coach + TeamRanks + Organization + Kicker) = (10+ 10+ 20+ 30+ NULL) it will return NULL instead of 70. You could add function like IFNULL(field,0). Like for example;

SELECT ID
FROM ranks 
ORDER BY (IFNULL(Fanbase,0)+IFNULL(Coach,0)+IFNULL(TeamRanks,0)+IFNULL(Organization,0)+IFNULL(Kicker,0)) DESC 
LIMIT 1;

However, the best approach is of course to make your field at default 0 and NOT NULL. Here I'm just concerned about What if ... ?

Upvotes: 1

The Impaler
The Impaler

Reputation: 48850

Use the MAX() function to aggregate columns. For example:

Select
  MAX(Fanbase + Coach + TeamRanks + Organization + Kicker) as max_total
from Ranks;

EDIT: To get the data of teams with the max_total value, you can do:

Select
  ID, 
  (Fanbase + Coach + TeamRanks + Organization + Kicker) as total,
  Fanbase, 
  Coach, 
  TeamRanks,
  Organization,
  Kicker
from Ranks
where Fanbase + Coach + TeamRanks + Organization + Kicker = (
  Select
    MAX(Fanbase + Coach + TeamRanks + Organization + Kicker) as max_total
  from Ranks
)

This query will return all the teams in first position, in case there are multiple ones.

Upvotes: 0

Related Questions