ORA-00937 not a single-group group function when using CASE Condition

I’ve been searching for a few hours now, on how to resolve an ORA-00937: not a single-group group function and none of them seem to explain why I am getting the error. My understanding is, when applying an aggregate function in the select statement on one or more columns, than you should include the columns that are not aggregated in the select by statement. I’ve done that, however the error always occurs at line 2, column 9 which is where the c.data_entry_date is located on the second line below. This error only occurs when I apply the logic from this portion of the query:

SUM(CASE 
    WHEN EXTRACT(YEAR FROM a.issue_date) < 1986 THEN
        CASE
          WHEN floor((c.data_entry_date - to_date(TO_CHAR(a.issue_date, 'DD-MON') || '-1986'))/365) between 0 and 5 then (7*(sum(a.area_in_hectares)))
          WHEN floor((c.data_entry_date - to_date(TO_CHAR(a.issue_date, 'DD-MON') || '-1986'))/365) between 6 and 10 then (10*(sum(a.area_in_hectares)))
          WHEN floor((c.data_entry_date - to_date(TO_CHAR(a.issue_date, 'DD-MON') || '-1986'))/365) between 11 and 15 then (15*(sum(a.area_in_hectares)))
          WHEN floor((c.data_entry_date - to_date(TO_CHAR(a.issue_date, 'DD-MON') || '-1986'))/365) between 16 and 20 then (20*(sum(a.area_in_hectares)))
          WHEN floor((c.data_entry_date - to_date(TO_CHAR(a.issue_date, 'DD-MON') || '-1986'))/365) between 21 and 25 then (25*(sum(a.area_in_hectares)))
          WHEN floor((c.data_entry_date - to_date(TO_CHAR(a.issue_date, 'DD-MON') || '-1986'))/365) between 26 and 30 then (30*(sum(a.area_in_hectares)))
          WHEN floor((c.data_entry_date - to_date(TO_CHAR(a.issue_date, 'DD-MON') || '-1986'))/365) between 31 and 35 then (35*(sum(a.area_in_hectares)))
          WHEN floor((c.data_entry_date - to_date(TO_CHAR(a.issue_date, 'DD-MON') || '-1986'))/365) between 36 and 40 then (40*(sum(a.area_in_hectares)))
          WHEN floor((c.data_entry_date - to_date(TO_CHAR(a.issue_date, 'DD-MON') || '-1986'))/365) between 41 and 45 then (45*(sum(a.area_in_hectares)))
          WHEN floor((c.data_entry_date - to_date(TO_CHAR(a.issue_date, 'DD-MON') || '-1986'))/365) between 46 and 50 then (50*(sum(a.area_in_hectares)))
        END
    END)

If I were to comment that specific CASE logic, then the query runs successfully. That being said, the CASE logic is fairly new to me. I’ve tried some creative Googling to try to find some sort of solution. I’ve played around with various columns in the group by and I can’t see to get it to work. I’m wondering if the aggregation of SUM in that CASE logic using, the c.data_entry_date column is conflicting with how I used it in the first column in the SELECT statement? Here’s my broader code if anyone wants to tackle a solution:

select 
TO_CHAR(c.data_entry_date,'yyyy-mm') as "Year-Month",
c.event_type_code "Event Code",
d.description "Event Description",
e.description "Title Type",
sum(a.area_in_hectares) "Sum hectares",
Count(unique(a.t_number_id)) "Count of T Number IDs",
CASE
  WHEN c.event_type_code = 'L_SPAY' and a.tenure_type_code = 'C' and a.tenure_sub_type_code = 'L' THEN (10*(sum(a.area_in_hectares)))

  WHEN c.event_type_code = 'L_SPAY' and a.tenure_type_code = 'C' and a.tenure_sub_type_code = 'S' THEN 
    SUM(CASE 
        WHEN EXTRACT(YEAR FROM a.issue_date) < 1986 THEN
            CASE
              WHEN floor((c.data_entry_date - to_date(TO_CHAR(a.issue_date, 'DD-MON') || '-1986'))/365) between 0 and 5 then (7*(sum(a.area_in_hectares)))
              WHEN floor((c.data_entry_date - to_date(TO_CHAR(a.issue_date, 'DD-MON') || '-1986'))/365) between 6 and 10 then (10*(sum(a.area_in_hectares)))
              WHEN floor((c.data_entry_date - to_date(TO_CHAR(a.issue_date, 'DD-MON') || '-1986'))/365) between 11 and 15 then (15*(sum(a.area_in_hectares)))
              WHEN floor((c.data_entry_date - to_date(TO_CHAR(a.issue_date, 'DD-MON') || '-1986'))/365) between 16 and 20 then (20*(sum(a.area_in_hectares)))
              WHEN floor((c.data_entry_date - to_date(TO_CHAR(a.issue_date, 'DD-MON') || '-1986'))/365) between 21 and 25 then (25*(sum(a.area_in_hectares)))
              WHEN floor((c.data_entry_date - to_date(TO_CHAR(a.issue_date, 'DD-MON') || '-1986'))/365) between 26 and 30 then (30*(sum(a.area_in_hectares)))
              WHEN floor((c.data_entry_date - to_date(TO_CHAR(a.issue_date, 'DD-MON') || '-1986'))/365) between 31 and 35 then (35*(sum(a.area_in_hectares)))
              WHEN floor((c.data_entry_date - to_date(TO_CHAR(a.issue_date, 'DD-MON') || '-1986'))/365) between 36 and 40 then (40*(sum(a.area_in_hectares)))
              WHEN floor((c.data_entry_date - to_date(TO_CHAR(a.issue_date, 'DD-MON') || '-1986'))/365) between 41 and 45 then (45*(sum(a.area_in_hectares)))
              WHEN floor((c.data_entry_date - to_date(TO_CHAR(a.issue_date, 'DD-MON') || '-1986'))/365) between 46 and 50 then (50*(sum(a.area_in_hectares)))
            END
        END)

END AS Revenue
from
tenure a, 
tenure_event_xref b,
event c,
event_type_code d,
title_type_code e
where
a.t_number_id = b.t_number_id
and b.e_number_id = c.e_number_id
and c.event_type_code = d.event_type_code
and a.title_type_code = e.title_type_code

group by TO_CHAR(c.data_entry_date, 'yyyy-mm'), c.event_type_code, d.description, e.description, a.tenure_type_code, a.tenure_sub_type_code

Much appreciated!

Answer

Remove the sum() in the then clauses. They case is already the argument of a sum(). There is no need to do a sum() within a sum() — it is not allowed.

Advertisement

Answer