sql - 为几行和某些值生成分数并将所有值相加

Question

我有一张如下表

    CustomerNumber  CustomerName    Type    Gender  Age Nationality Residance
    584             John Denver     Retail  Male    50  English     Domestic
    137             Jennifer Hazel  Retail  Female  34  Jamaican    Abroad
    547             Daniel Gorbo    Retail  Male    78  French      Domestic

我需要根据类型、性别、年龄、国籍和居住值生成总分。例如，

• 对于性别，男性值生成“1”，女性值生成“0”
• 对于年龄，如果大于 65，则生成“3”，否则生成“0”
• 对于国籍，如果不是英语，则生成 2，否则为 0

决赛桌应如下所示：

    CustomerNumber  CustomerName    Type    Gender  Age Nationality Residance   RiskScore
    584             John Denver     Retail  Male    50  English     Domestic    1
    137             Jennifer Hazel  Retail  Female  34  Jamaican    Abroad      2
    547             Daniel Gorbo    Retail  Male    78  French      Domestic    6

我试图在主代码中添加 sum 和“case when”但是我得到了“need group by function”的错误

sum( case when gender = 'Male' then 1 
        when age>65 then 3 
        when nationality = 'Abroad' then 2 else 0 end ) as RiskScore 

任何帮助，将不胜感激..

标签： sqlsql-serversumcase-when

Answer 1

如果我正确理解了这个问题，则不需要聚合。使用加法和CASE表达式计算风险评分：

SELECT 
   v.*,
   RiskScore = (
      CASE WHEN Gender = 'Male' THEN 1 ELSE 0 END +
      CASE WHEN Age > 65 THEN 3 ELSE 0 END +
      CASE WHEN Nationality <> 'English' THEN 2 ELSE 0 END
   )
FROM (VALUES
   (584, 'John Denver',    'Retail', 'Male',   50, 'French',   'Domestic'),
   (137, 'Jennifer Hazel', 'Retail', 'Female', 34, 'Jamaican', 'Abroad'),
   (547, 'Daniel Gorbo',   'Retail', 'Male',   78, 'English',  'Domestic')
) v (CustomerNumber, CustomerName, Type, Gender, Age, Nationality, Residance)

结果：

CustomerNumber CustomerName   Type   Gender Age Nationality Residance RiskScore
584            John Denver    Retail Male   50  French      Domestic  3
137            Jennifer Hazel Retail Female 34  Jamaican    Abroad    2
547            Daniel Gorbo   Retail Male   78  English     Domestic  4