not single-group grouping function while using regr_slope

Question

HI I try to run :

 select
  year,
  regr_slope(sum(sale_count),year) as slope,
from products
group by year

It throws "00937. 00000 - "not a single-group group function"" .When i delete year from select clause problem disapears. Shouldn't I be able to select column with which I'm grouping?

Oracle 11.2 sqldeveloper

ty for help !

Answer 1

It's because you're attempting to use a function (REGR_SLOPE) that can be either an aggregate (or analytical) function on the result of another aggregate (SUM) - use:

  SELECT x.year,
         REGR_SLOPE(sum_sales, x.year) AS slope
    FROM (SELECT y.year,
                 SUM(y.sale_count) AS sum_sales
            FROM PRODUCTS y
        GROUP BY y.year) x
GROUP BY x.year

Alternative using WITH clause (Oracle 9i+):

WITH sums AS (
     SELECT y.year,
            SUM(y.sale_count)
       FROM PRODUCTS y
   GROUP BY y.year)
  SELECT x.year,
         REGR_SLOPE(sum_sales, x.year) AS slope
    FROM sums x
GROUP BY x.year