时间戳

首页 > 解决方案 > SUM 3 个不同的“CASE”列，三个不同的总数

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

问题描述

我是新手，所以如果我问错了这个问题，我很抱歉。但我试图从使用 4 种不同情况的表中获取 4 种不同的 SUM。但我希望 id 只与总数一起列出一次。我会告诉你我有什么，我想得到什么。如果可能，请提供帮助。

    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

我的桌子

这就是我想要得到的。

这是我想要得到的表

这可能吗？

标签： sqlpostgresql

解决方案

使用聚合和分组依据

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

推荐阅读