How to select average from row result on oracle?-CodePudding

I am trying to calculate average from the result then join them.

My main query is here:

 SELECT
      A.USERNAME,
      A.TOTAL,
      A.MATH 

    FROM 
    (SELECT 
        B.USERNAME,     
       COUNT(CLASS_ID) TOTAL,   
       SUM(CASE WHEN ROOM_ID = '1001' THEN 1 ELSE 0 END) AS MATH
        FROM    A LESSON, STUDENT B
         WHERE  
          A.USERNAME = B.USERNAME                    
        AND A.CREATED_DATE >= TO_DATE(:created_date ,'YYYYMMDDHH24MISS')
        AND A.CREATED_DATE < TO_DATE(:created_end_date ,'YYYYMMDDHH24MISS')
        GROUP BY B.USERNAME 
        ORDER BY TOTAL DESC) A

It gives me:

|USERNAME|TOTAL|MATH|           
|John    |  480|159 |
|Dave    |  360|120 |
|Sarah   |  540|130 |
|James   |  361|185 |
|Kim     |  640|92  |
|Matt    |  11 |2   |
|Jane    |  8  |1   |

But I want to get like this:

|USERNAME|TOTAL|AVG_FROM_TOTAL|MATH|AVG_FROM_MATH|              
|John    |  480|      476     | 159|     137     |
|Dave    |  360|      476     | 120|     137     |
|Sarah   |  540|      476     | 130|     137     |
|James   |  361|      476     | 185|     137     |
|Kim     |  640|      476     |  92|     137     |

It's calculated manually like this

(480 360 540 361 640/5 = 476) /It will be removed the user Matt and Jane because too low from others/
(159 120 130 185 92/5 = 137) /removed Matt and Jane result/

How can I create query that I want to calculate? Is it possible to calculate average from the result?

CodePudding user response：

Simple as this:

SELECT A.USERNAME
     , A.TOTAL
     , AVG(A.TOTAL) OVER () AS avg_total
     , A.MATH
     , AVG(A.MATH) OVER () AS avg_math
  FROM (your derived table) A
 WHERE A.TOTAL > 20
;

These are window functions.

Feel free to remove any rows you wish from the derived table or after the derived table in the outer WHERE clause (which you can add).