sql-ORA-00937:不是单组分组功能 [英] sql - ORA-00937: not a single-group group function

问题描述

 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

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
group by location,home_team_name;

错误消息:ORA-00937:不是单组分组功能.当前在oracle live SQL上运行它.

Error message: ORA-00937: not a single-group group function. Currently running this on oracle live SQL.

关于如何解决此问题的任何想法?

Any ideas on how I can solve this?

推荐答案

运行聚合查询时，每个非聚合列都必须出现在GROUP BY子句中.您有两个UNION ed子查询:并且只有第一个具有未聚合的列(即location和home_team)，但是您碰巧将GROUP BY子句放在第二个子查询中，因为似乎要计算总数，可能不需要一个.您可以将GROUP BY子句放在第一个查询中，而不是第二个:

When running an aggregate query, every non-aggregated column must appear in the GROUP BY clause. You have two UNIONed subquery : and only the first one has non-aggregated columns (namely, location and home_team), however you happened to put the GROUP BY clause in the second one, that, as it seems to compute totals, probably does not need one. You could just place the GROUP BY clause in the first query instead of the second :

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
;

这篇关于sql-ORA-00937:不是单组分组功能的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！