I'm new to SQL and struggling with this query, any help much appreciated.

My table looks like this:

I'd like to calculate a total score as the max score from each level.

So in laymans: Total Score = Max score from level 1 Max score from level 2 Max score from level 3

How do I replicate that in SQL?

CodePudding user response：

You could use SUM on the result of a sub-query:

SELECT
   SUM(max_score) AS total_score 
FROM
   (
      SELECT
         MAX(score) AS max_score 
      FROM
         YOUR_TABLE_NAME 
      GROUP BY
         level
   )
   AS max_scores

CodePudding user response：

Hi I think you are searching for this solution. I hope it will fix your question.

WITH A as (
Select MAX(score) as "MAX order" FROM "YOUR TABLE" GROUP BY level)
SELECT SUM("MAX score per level") FROM a

CodePudding user response：

select distinct sum(max(score)) over () as "Total Score" from T group by level;

You can do the two aggregates together with group by and then a windowed sum. Distinct takes care of removing what would be the same sum repeated for every group. (In general I would encourage you to avoid too much reliance on select distinct.)

The other approaches using either CTEs/derived tables are perfectly fine. A beginner's solution would probably look more like this:

select t.level, sum(maxScore) as "Total Score"
from T t inner join
    (select level, max(score) as maxScore from T group by level) as m
        on m.level = t.level
group by t.level;

Depending on the platform you might get this to work

select level,
    sum((select max(score) from t t2 where t2.level = t.level)) as "Total Score"
from T t
group by level