如何在SQL中对别名total求和并按Station_equipment分组？

要实现按Station_equipment分组求和，同时保留「X Days Y Hours Z Minutes」的格式，不能直接对拼接后的字符串求和，得先计算原始时长的数值总和，再转换格式。以下是修改后的SQL代码：

WITH FailureDurations AS (
    SELECT 
        CONCAT(a.Station_Name,' ',c.Equipment_Name) AS Station_equipment,
        -- 计算单条记录的故障总分钟数：天数*1440 + 小时数*60 + 分钟数
        (DATEPART(DAY, a.fail_to - a.fail_from) - 1)*1440 
        + DATEPART(HOUR, a.fail_to - a.fail_from)*60 
        + DATEPART(MINUTE, a.fail_to - a.fail_from) AS TotalMinutes
    FROM vw_AllPotentialFailures_ByPeriod AS a 
    LEFT JOIN EquipmentFailures AS b ON a.PotentialFailure_ID = b.PotentialFailure_ID 
    LEFT JOIN Equipment AS c ON b.Equipment_ID = c.Equipment_ID 
    WHERE fail_from BETWEEN '14 Feb 2020' AND '14 Feb 2023 23:59'
      AND a.KPI_Applicable = 'LiftsNR'
)
SELECT 
    Station_equipment,
    -- 将总分钟数转换为带单位的格式
    CONCAT(
        CASE WHEN TotalDays > 0 THEN CAST(TotalDays AS NVARCHAR(100)) + ' Days ' ELSE '' END,
        CASE WHEN TotalHours > 0 THEN CAST(TotalHours AS NVARCHAR(100)) + ' Hours ' ELSE '' END,
        CASE WHEN TotalMinutes > 0 THEN CAST(TotalMinutes AS NVARCHAR(100)) + ' Minutes ' ELSE '' END
    ) AS total
FROM (
    SELECT 
        Station_equipment,
        -- 总分钟数换算为日、时、分
        SUM(TotalMinutes) / 1440 AS TotalDays,
        (SUM(TotalMinutes) % 1440) / 60 AS TotalHours,
        SUM(TotalMinutes) % 60 AS TotalMinutes
    FROM FailureDurations
    GROUP BY Station_equipment
) AS GroupedDurations
ORDER BY Station_equipment;

关键说明： #

1. CTE表FailureDurations：先计算每条故障记录的总分钟数，把日、时、分统一转换成可求和的数值类型，避免字符串无法直接计算的问题。
2. 分组求和：按Station_equipment分组后，对总分钟数求和，再将总和换算回日、时、分。
3. 格式拼接：用CASE判断各单位是否大于0，只拼接有值的单位，保持和原格式一致的输出效果。

内容的提问来源于stack exchange，提问作者Sorcha