CODEWITHSUNDEEP
sql
apkos99
apkos99

Reputation: 109

sql query error

select E.Job_ID, count(*), max(Employee_Salary) as kos, avg(Employee_Salary)
    from Employee E  
    inner join JOB D on E.Job_ID = D.Job_ID
    group by E.Job_ID

I want to order my result by D.Functio but i have this error 

Msg 8120, Level 16, State 1, Line 1
Column 'JOB.Functio' is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause.

Upvotes: 1

Views: 57

Answers (3)

Kaf
Kaf

Reputation: 33809

Include D.Functio in the Select and Group by 

select E.Job_ID, D.Functio,
     count(*), max(Employee_Salary) as kos, avg(Employee_Salary)
from Employee E  
inner join JOB D on E.Job_ID = D.Job_ID
group by E.Job_ID, D.Functio
order by D.Functio

Upvotes: 1

Ilion
Ilion

Reputation: 6872

You want to add it to your GROUP BY if it's a descriptive column (job function?)

select E.Job_ID, count(*), max(Employee_Salary) as kos, avg(Employee_Salary)
    from Employee E  
    inner join JOB D on E.Job_ID = D.Job_ID
    group by E.Job_ID, D.Functio
    order by D.Functio

Otherwise in the ordering you need to apply an aggregate function like min(), max(), avg() etc.

Upvotes: 1

Gordon Linoff
Gordon Linoff

Reputation: 1269533

What is unclear about the error message? You are trying to order by something that is not being aggregated. Here is one solution:

order by max(d.Functio)

The problem is that the alias "job" is not known. You need to use the "d" that you assigned to the table.

This should work, since there is presumably one job.Functio per job id. Another fix is to change the group by to:

group by e.Job_ID, d.Job.Functio

Upvotes: 2

Related Questions