Query to find average fee paid by students in department wise

Query to find average fee of their department – Learn how to write the queries to find average fee paid by students in department wise with various examples.

For base table reference please click on the  STD URL

Query to select the Students who paid more than the average fee of the department studying in.

Query 1: using in line views

SELECT s.*
FROM STD S,
( SELECT deptno,AVG(fee) FEE FROM STD GROUP BY deptno
) A
WHERE s.deptno=A.DEPTNO
AND S.FEE > A.FEE;

Query 2: using co related query

SELECT *
FROM STD S
WHERE s.fee >
(SELECT AVG(FEE) FROM STD A WHERE s.deptno=A.DEPTNO GROUP BY a.deptno
);

Query 3: using analytical function

SELECT STDNO,SNAME,joindate,FEE,DEPTNO
FROM
  (SELECT STDNO,
    SNAME,
    joindate,
    FEE,
    DEPTNO,
    AVG(FEE) OVER(partition BY DEPTNO) AVERAGE
  FROM STD
  )
WHERE FEE> AVERAGE;

Output

Below is the output for all the above queries

Query to select the Students who paid equal to the average fee of the department studying in.

Query 1: using in line views

SELECT S.*
FROM STD S,
( SELECT deptno,AVG(fee) FEE FROM STD GROUP BY deptno
) A
WHERE s.deptno=A.DEPTNO
AND S.FEE = A.FEE;

Query 2: using co related query

SELECT *
FROM STD S
WHERE s.fee =
(SELECT AVG(FEE) FROM STD A WHERE s.deptno=A.DEPTNO GROUP BY a.deptno
);

Query 3: using analytical function

SELECT 
    STDNO,
    SNAME,
    joindate,
    FEE,
    DEPTNO
FROM
(SELECT STDNO,
SNAME,
joindate,
FEE,
DEPTNO,
AVG(FEE) OVER(partition BY DEPTNO) AVERAGE
FROM STD
)
WHERE FEE= AVERAGE;

Output

Below is the output for all the above queries