Jay Desai Jay Desai - 8 days ago 6
SQL Question

How to use output of inner query in SQL

I have 2 tables in Oracle 11g database i.e.

EARNEDRATING
and
EMPLOYEE
.

EMP_NUM RTG_CODE EARNRTG_DATE
------------------------------------
101 CFI 18-Feb-98
101 CFII 15-Dec-05
101 INSTR 8-Nov-93
101 MEL 23-Jun-94
101 SEL 21-Apr-93


EMP_NUM EMP_LNAME EMP_FNAME
-----------------------------
101 Kolmyoz George


I want to write the query which find emp_num, emp_fname, emp_lname for all employee and their number of earned ratings.

I tried like this

select
er.emp_num, e.emp_fname, e.emp_lname,
count(er.rtg_code) as "Number of Earned Rating"
from
earnedrating er
join
employee e on er.emp_num=e.emp_num
group by
er.emp_num;


but getting this error


not a GROUP BY expression

Answer

You have to put in group by all the colums that are not in an aggregate function:

select er.emp_num,
       e.emp_fname,
       e.emp_lname,
       count(er.rtg_code) "Number of Earned Rating" 
from earnedrating er,employee e 
where er.emp_num = e.emp_num  
group by er.emp_num , e.emp_fname, e.emp_lname;
Comments