0

Trying to write a view in oracle 11g
I want this code to return just the fist name and the last name of a employee who has .
But it keeps on giving me the error : "ORA-00979: not a GROUP BY expression"

create or replace view highesttotalcommission as (
    select *
    from  (     select e.firstname|| ' ' ||e.lastname "Highest commission"
            from employee e
            inner join salesperson sp
            on e.employeeID = sp.employeeID
            inner join salesinvoice si
            on si.salespersonid = e.employeeID
            group by si.salespersonid
            order by SUM(si.price * sp.commissionpct) desc )
    where ROWNUM = 1);
Ned Charles
  • 5
  • 3

1 Answers1

0

I think the error is pretty obvious. You need to include the columns or expression in the select in the group by:

create or replace view highesttotalcommission as
    select *
    from  (select (e.firstname || ' ' || e.lastname) as "Highest commission"
           from employee e inner join
                salesperson sp
                on e.employeeID = sp.employeeID inner join
                salesinvoice si
                on si.salespersonid = e.employeeID
            group by si.salespersonid, e.firstname|| ' ' ||e.lastname
            order by SUM(si.price * sp.commissionpct) desc
          ) e
    where ROWNUM = 1;
Gordon Linoff
  • 1,242,037
  • 58
  • 646
  • 786