时间戳

首页 > 解决方案 > 合并同一张表中的两行并计算总数

sql - 合并同一张表中的两行并计算总数

问题描述

我有一个有两列的表，如下所示：

total | measure
________________
1     | private vehicle use
4     | private car use
7     | other

我想将前两行合并为一行，将合并行的度量（strEUCategory）重命名为“私人车辆使用”。我也想合并总数。我已经研究了 CASE 和 PIVOT，但在计算总数的同时无法让它工作。

这是我的查询。

SELECT  count(m.strEUCategory) as total , m.strEUCategory as measure
                from  laqm2_AQAP_measures  m
                where m.strYearIntroduced <=2020 and m.strCompletionYear >=2020
                group by m.strEUCategory
                order by m.strEUCategory

标签： sqlstringcountmariadbcase

解决方案

我认为你想要一个case表达式和聚合：

select
    case when strEUCategory = 'private car use' then 'private vehicule use' else strEUCategory end as measure,
    count(*) cnt
from laqm2_AQAP_measures
where strYearIntroduced <= 2020 and strCompletionYear >= 2020
group by measure
order by measure

如果您想对新类别名称进行更多控制，则：

select
    case when strEUCategory in ('private car use', 'private vehicle use')  
        then 'Private Vehicule Use' 
        else strEUCategory 
    end as measure,
    count(*) cnt
from laqm2_AQAP_measures
where strYearIntroduced <= 2020 and strCompletionYear >= 2020
group by measure
order by measure

推荐阅读