使用union和case时

select Item, sum(case when status = 'good' then 1 else 0 end) as good, 
 sum ( case when status = 'bad' then 1 else 0 end) as bad
from (select Item, Status1 as status
      from table_name
      union all
      select Item, Status2
      from table_name
     ) t
group by Item;

可以使用union all和条件聚合

select item, count(case when status1='good' then 1 end) as good,
count(case when status1='bad' then 1 end) as bad
from
(
select item , status1 from tablename
union all
select item , status2 from tablename
)A group by item

不需要联合，只要应用一些逻辑。

select Item
  ,sum(case when Status1 = 'Good' then 1 else 0 end +
       case when Status2 = 'Good' then 1 else 0 end) as good
  ,sum(case when Status1 = 'Bad' then 1 else 0 end +
       case when Status2 = 'Bad' then 1 else 0 end) as bad
from tab
group by Item

或

select Item
  ,count(case when Status1 = 'Good' then 1 end) +
   count(case when Status2 = 'Good' then 1 end) as good
  ,count(case when Status1 = 'Bad' then 1 end) +
   count(case when Status2 = 'Bad' then 1 end) as good
from tab
group by Item

使用 UNION ALL &do聚合：

select item, sum(status = 'good'), sum(status = 'bad')
from (select item, status1 as status
      from table t
      union all
      select item, status2
      from table t
     ) t
group by item;

您可以使用子查询，然后在外部查询中应用sum函数

select distinct(item) as item, sum(S1G+S2G) as Good,sum(S1B+S2B) as Bad from (  select item, CASE WHEN status1 ='Good' THEN 1 ELSE 0 END as S1G, CASE WHEN status2 ='Good' THEN 1 ELSE 0 END as S2G,  CASE WHEN status2 ='Bad' THEN 1 ELSE 0 END as S2B,  CASE WHEN status1 ='Bad' THEN 1 ELSE 0 END as S1B from t1 ) as b group by item

这是演示