CODEWITHSUNDEEP
sql
Mike Goedken
Mike Goedken

Reputation: 53

simple error "ORA-00979: not a GROUP BY expression"

I am trying to List the full name and the total number of transactions handled by the full time employee and Rank the results in descending order on the total number of transactions.

This is what I have 

select 
    concat( e.efirst, e.elast ) ename, 
    count(*) total_transactions
from 
    transactions t 
    join employees e on t.employeeid = e.employeeid
where 
    e.etype = 'Fulltime' 
group by
    t.employeeid 
order by
    total_transactions desc;

Upvotes: 0

Views: 59

Answers (3)

Dai
Dai

Reputation: 155075

When computing aggregates when grouped by a foreign key you'll want to JOIN the principal relation (in this case employee) in an outer-query, as it's a separate concern from the aggregate.

SELECT
    CONCAT( CONCAT( employee.efirst, ' ' ), employee.elast ) AS employee_name, 
    employee_id,
    total_transactions
FROM
    (
        SELECT
            employeeid AS employee_id,
            COUNT(*) AS total_transactions
        FROM
            transactions
        GROUP BY
            employeeid
    ) AS sq
    INNER JOIN employee ON sq.employee_id = employee.employeeid
WHERE
    employee.etype = 'Fulltime'
ORDER BY
    total_transactions desc;

Upvotes: 4

Gordon Linoff
Gordon Linoff

Reputation: 1269673

The group by expressions should match the unaggregated expressions in the select, so:

select ( e.efirst || ' ' || e.elast ) as ename, 
       count(*) total_transactions
from transactions t join
     employees e
     on t.employeeid = e.employeeid
where e.etype = 'Fulltime' 
group by ( e.efirst || ' ' || e.elast ) 
order by total_transactions desc;

I added a space between the names as well and switched to using the concatenation operator.

This assumes that employee names are distinct. If this is not the case, then you should be including the employee id in the select, s you can distinguish amongst those with the same name.

Upvotes: 0

forpas
forpas

Reputation: 164089

Add concat(e.efirst, e.elast) to group by:

select 
    concat(e.efirst, e.elast) ename, 
    count(*) total_transactions
from 
    transactions t 
    join employees e on t.employeeid = e.employeeid
where 
    e.etype = 'Fulltime' 
group by
    t.employeeid, concat(e.efirst, e.elast) 
order by
    total_transactions desc;

Upvotes: 1

Related Questions