Oracle sql query : implementing an equation into a select group by

0
votes

I have the following weighted average equation I'm trying to put into the select clause

(( operating_hrsA * component countA ) + ( operating_hrsC * component countB ) + ( operating_hrsC * component countC )) / total compont_countABC

The select I'm trying to put it into is: 

 SELECT reporting_date_from,
            reporting_date_to,
            b_name,
            oflag,
            component_type,
            SUM (component_count) AS component_count,
            --AVG (average_operating_hours) AS average_operating_hours,
            sum((average_operating_hours * component_count) / sum(component_count))  AS average_operating_hours


       FROM DEVICE
       where reporting_date_From = '01-JAN-2017' and b_name like '430%' 
   GROUP BY reporting_date_from,
            reporting_date_to,
            b_name,
            oflag,
            component_type;

error I'm getting is :

Error at line 1 ORA-00937: not a single-group group function

table schema : column | data type | Null ? reporting_date_From date N reporting_date_to date N b_name varchar2(100 byte) Y oflag varchar2(50 byte) Y component_type varchar2(50 byte) Y average_operating_hours number Y

data sample data sample any ideas what I'm doing wrong with this calculation?
thanks in advance

2
sqloracle
seems you have nested sum .. – ScaisEdge
are nested sum()'s not allowed in a select? – phill
Generally nested sums are not allowed, there is one exception to that rule, but your query is not this case. – krokodilko
not directly ... you should select the firts leve sum and then perform the seconnd leve – ScaisEdge
can you provide an example on how to do that? really appreciate the help – phill

2 Answers

0
votes

You could use a subselect for get the result and then perform the calculation that implies nested sum eg:

select  reporting_date_from,
            reporting_date_to,
            b_name,
            oflag,
            component_type,
            component_count, 
            average_operating_hours, 
            my_value/component_count as average_operating_hours
  from(           
      SELECT reporting_date_from,
              reporting_date_to,
              b_name,
              oflag,
              component_type,
              SUM (component_count) AS component_count,
              AVG (average_operating_hours) AS average_operating_hours,
              sum((average_operating_hours * component_count) )  AS my_value,

         FROM DEVICE
         where reporting_date_From = '01-JAN-2017' and b_name like '430%' 
     GROUP BY reporting_date_from,
              reporting_date_to,
              b_name,
              oflag,
              component_type

    )  t
    ;
0
votes

You can do this using a window function. Change the partition by columns if needed based on the table structure and the desired result.

SELECT DISTINCT
reporting_date_from,
reporting_date_to,
b_name,
oflag,
component_type,
sum(component_count) over(partition by reporting_date_from,reporting_date_to,b_name,oflag) AS component_count,
sum(average_operating_hours * component_count) over(partition by reporting_date_from,reporting_date_to,b_name,oflag)
/ sum(component_count) over()  AS average_operating_hours,
FROM DEVICE
where reporting_date_From = '01-JAN-2017' and b_name like '430%'