sql - ORA-00937: not a single-group group function

Question

 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;

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?

Move the `group by` to the first select. And have a look at `grouping sets` to get both in a single select. — dnoeth, Jan 12 '19 at 12:22

score 3 · Answer 1 · answered Jan 12 '19 at 12:27

I recommend 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), () );

That is, repeating the query is unnecessary. I also changed the date comparisons to use actual dates. I find this more readable and maintainable than extracting date parts.

@sammywemmy . . . I find it surprising that you prefer the more verbose solution. — Gordon Linoff, Jan 12 '19 at 22:21
I learnt a lot from both answers and understand sql more .it isn’t one over the other, I can only tick off one answer tho — sammywemmy, Jan 13 '19 at 23:30

score 2 · Accepted Answer · answered Jan 12 '19 at 12:27

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
;

Thanks for this. I really appreciate – sammywemmy Jan 12 '19 at 21:02 — sammywemmy, Jan 12 '19 at 21:02

sql - ORA-00937: not a single-group group function

2 Answers2

Linked