sql-ORA-00937:不是单个组函数

我推荐 grouping sets :

select coalesce(location, 'Total') as location,
       coalesce(home_team_name, 'Total') as home_team_name,
       sum(case when match_date >= date '2018-01-01' and 
                     match_date < date '2018-02-01'
                then 1 else 0
           end) as january_2018,
       sum(case when match_date >= date '2018-02-01' and 
                     match_date < date '2018-03-01'
                then 1 else 0
           end) as february_2018,
       sum(case when match_date >= date '2018-03-01' and 
                     match_date < date '2018-04-01'
                then 1 else 0
           end) as march_2018,
       sum(case when match_date >= date '2018-01-01' and 
                     match_date < date '2019-01-01'
               then 1 else 0
           end) as total_2018
from match_results
group by grouping sets ( (location, home_team_name), () );

也就是说,不需要重复查询。我还将日期比较改为使用实际日期。我发现这比提取日期部分更具可读性和可维护性。

GROUP BY UNION ed子查询:并且只有第一个子查询具有非聚合列(即, location 和 home_team ),但是你碰巧把 分组 分组 第一个查询(而不是第二个查询)中的子句:

select
    location,
    home_team_name,
    count(case when extract(year from match_date)='2018' and extract(month from match_date)=1 then 1 end) january_2018,
    count(case when extract(year from match_date)='2018' and extract(month from match_date)=2 then 1 end) february_2018,
    count(case when extract(year from match_date)='2018' and extract(month from match_date)=3 then 1 end) march_2018,
    count(case when extract(year from match_date)='2018' then 1 end) Total
from match_results
group by location,home_team_name

union all

select 
    'total' as location,
    'total' as home_team_name,
    count(case when extract(month from match_date)=1 then 1 end) january_2018,
    count(case when extract(month from match_date)=2 then 1 end) february_2018,
    count(case when extract(month from match_date)=3 then 1 end) march_2018,
    count(case when extract(year from match_date)='2018' then 1 end) Total
from match_results
;