SQL Not a Single-Group Group Function

SQL not a single-group group function

Well the problem simply-put is that the SUM(TIME) for a specific SSN on your query is a single value, so it's objecting to MAX as it makes no sense (The maximum of a single value is meaningless).

Not sure what SQL database server you're using but I suspect you want a query more like this (Written with a MSSQL background - may need some translating to the sql server you're using):

SELECT TOP 1 SSN, SUM(TIME)
FROM downloads
GROUP BY SSN
ORDER BY 2 DESC

This will give you the SSN with the highest total time and the total time for it.

Edit - If you have multiple with an equal time and want them all you would use:

SELECT
SSN, SUM(TIME)
FROM downloads
GROUP BY SSN
HAVING SUM(TIME)=(SELECT MAX(SUM(TIME)) FROM downloads GROUP BY SSN))

SQL not a single group group function error

When you include an aggregate function (like avg, sum) in your query, you must group by all columns you aren't aggregating.

SELECT LGBRAND.BRAND_ID, LGBRAND.BRAND_NAME, AVG(LGPRODUCT.PROD_PRICE)AS AVGER
FROM LGPRODUCT, LGBRAND
WHERE LGPRODUCT.BRAND_ID = LGBRAND.BRAND_ID
GROUP BY
LGBRAND.BRAND_ID,
LGBRAND.BRAND_NAME
ORDER BY BRAND_NAME

Error - not a single-group group function

Try using a subquery:

select e.*
from (select employee_id, count(employee_id)
      from hr.job_history 
      group by employee_id
      order by count(*) desc
     ) e
where rownum = 1;

My guess is that your version of Oracle may not support fetch.

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

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
;

Why it shows not a single-group function?

Your query is invalid, because you aggregate your rows to one result row, but tell the DBMS to show the country_id. Which?

As to the query: Just join the tables and count:

select
  min(country_id),
  count(distinct country_id) as country_count,
  count(*) as location_count
from hr.countries c
join hr.locations l using (country_id)
where c.region_id = 2;

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

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.

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

Rather than the MIN(...) KEEP (...) aggregation function, you want to use MIN(...) KEEP (...) OVER (...) analytic function or, possibly without the KEEP, use MIN(...) OVER (PARTITION BY ...):

select
        o.customer_id,
        c.cust_first_name,
        c.cust_last_name,
        c.income_level,
        to_char(o.order_date, 'DD-MON-YY HH12:MI') as order_date,
        ROW_NUMBER() over (partition by o.customer_id order by o.order_date)
          as order#,
        o.order_total,
        lag(o.order_total, 1, 0)
          over (partition by o.customer_id order by o.customer_id) 
        + lag(o.order_total, 2, 0)
          over (partition by o.customer_id order by o.customer_id)
          as last_two_orders_sum,
        MIN(o.order_date) OVER (PARTITION BY o.customer_id) as first_order_date,
        MIN(o.order_total) KEEP (DENSE_RANK FIRST ORDER BY o.order_date)
                           OVER (PARTITION BY o.customer_id) as first_order_total
from    orders o
        INNER JOIN customers c
        ON o.customer_id = c.customer_id

Note: Also, use ANSI join syntax rather than the legacy comma-join syntax.

I know that this is a limitation in Oracle 11g and this issue is fixed in 12c, but I still need some kind of workaround.

No, this is not something that you fix by upgrading to a later version. It is a fundamental issue with your query that you are using an mix of aggregated and non-aggregated columns and, as the error message implies, you do not have a GROUP BY clause that includes all of the non-aggregated columns.

The solution is to not use any aggregation functions and change to only use analytic functions.

db<>fiddle here

---

Related Topics