I'm new to SQL and struggling with this query, any help much appreciated.
My table looks like this:
| id | score | level |
|---|---|---|
| 2 | 250 | 1 |
| 2 | 350 | 1 |
| 2 | 850 | 2 |
| 2 | 260 | 2 |
| 2 | 750 | 3 |
| 2 | 560 | 3 |
I'd like to calculate a total score as the max score from each level.
So in layman's: Total Score = Max score from level 1 + Max score from level 2 + Max score from level 3
How do I replicate that in SQL?
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
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
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
SELECT *, (SELECT SUM(table.score) WHERE level = N) AS 'level_1_score'.....