sql - 总计3个不同的“CASE”列，总计三个不同的总数

我是新来的，所以如果我问错了，我很抱歉。但是我试图从我的表中得到4个不同的和，使用4个不同的情况。但我只想把身份证和总数一起列一次。我会告诉你我有什么，我想得到什么。如果可能的话，请帮忙。

    SELECT schools.name, articles.competition_place,

    Case when articles.competition = 'yes' and competition_place = '1' then int '100'
       when articles.competition = 'yes' and competition_place = '2' then int '60'
       when articles.competition = 'yes' and competition_place = '3' then int '20'
    ELSE 0 end AS "Competition_Score",

    Case when articles.out_reach = 'yes' then int '30'
        ELSE 0 end AS "out_reach_Score",

    CASE when schools.school_id is not null then int '5'
        ELSE 0 end as "article_score",

    (Case when articles.competition = 'yes' and competition_place = '1' then int '100'
        when articles.competition = 'yes' and competition_place = '2' then int '60'
        when articles.competition = 'yes' and competition_place = '3' then int '20'
        ELSE 0 end) +
    (Case when articles.out_reach = 'yes' then int '30'
        ELSE 0 end) +
    (CASE when schools.school_id is not null then int '5'
        ELSE 0 end) as "total_score"
    from articles
    join clubs on articles.club_id = clubs.club_id
    join schools on clubs.school_id = schools.school_id

My table that I have
这就是我想要的。
This is the table I'm trying to get
这可能吗？

最佳答案

使用聚合和分组方式

SELECT schools.name, articles.competition_place,

sum(Case when articles.competition = 'yes' and competition_place = '1' then int '100'
   when articles.competition = 'yes' and competition_place = '2' then int '60'
   when articles.competition = 'yes' and competition_place = '3' then int '20'
ELSE 0 end) AS "Competition_Score",

sum(Case when articles.out_reach = 'yes' then int '30'
    ELSE 0 end) AS "out_reach_Score",

sum(CASE when schools.school_id is not null then int '5'
    ELSE 0 end) as "article_score",

sum((Case when articles.competition = 'yes' and competition_place = '1' then int '100'
    when articles.competition = 'yes' and competition_place = '2' then int '60'
    when articles.competition = 'yes' and competition_place = '3' then int '20'
    ELSE 0 end)) +
sum((Case when articles.out_reach = 'yes' then int '30'
    ELSE 0 end)) +
sum(CASE when schools.school_id is not null then int '5'
    ELSE 0 end)) as "total_score"
from articles
join clubs on articles.club_id = clubs.club_id
join schools on clubs.school_id = schools.school_id
group by schools.name, articles.competition_place