Derived Fields (SQL)

  • Thread starter Thread starter lizardmandy
  • Start date Start date
L

lizardmandy

Guest
Having a bit of trouble with a project, here...

I have a table that includes Employee's Names, Salaries, and Departments
(I also have a Department table, listing each Department)

I need to have a query that will return a listing of each department, the highest salary within the department, and the number of employees working in the department.

The only thing I've been able to do so far is construct a query that prompts the user for the department number, then returns the max salary or the # of employees. I'd like to have all that data in one output when the query is run, without prompting the user to input any values.

Thank you for any advice you might have for me!
 
Hi - and welcome!

You need to create a query that joins the Employee table and Department table (should be straightforward if you have the Department information stored as a foreign key for your Employee table).

You can then use the aggregate functions to get the MAX of the salary and the COUNT of the employees.

Let me know if you still have questions.

- g
 
Thanks for the welcome. :)

(EDIT)

Ooh I figured it out, mostly, I think! This is the output I get from the SQL I run:

SELECT MAX(EMP_SALARY) AS [Maximum Salary], COUNT(EMP_SSN) AS [Total Employees]
FROM EMPLOYEE
GROUP BY (EMP_DEPARTMENT);

sampleoutput0oa.png


However, I would love to have a column in there with the department numbers so I can identify which one belongs to which department. I thought this would be as simple as:

SELECT DEPT_NUM, MAX(EMP_SALARY) AS [Maximum Salary], COUNT(EMP_SSN) AS [Total Employees]
FROM EMPLOYEE, DEPARTMENT
GROUP BY (EMP_DEPARTMENT);

But then when I try to run this query, I get this error:

"You tried to execute a query that does not contain the specified expression 'DEPT_NUM' as part of an aggregate function."

Is there a way to get this to work? Sorry to be such a bother, I'm on the last legs of a school project and my textbook is confusing me. I'm going to keep working on this to see if I can figure it out but any further input would be greatly appreciated!

Thank you for the help so far! :D

- Mandy
 
Last edited:
Oh wow, nevermind, hehe...I was able to figure it out from just that one response after all!

SELECT DISTINCT EMP_DEPARTMENT, MAX(EMP_SALARY) AS [Maximum Salary], COUNT(EMP_SALARY) AS [Total Employees]
FROM EMPLOYEE
GROUP BY (EMP_DEPARTMENT);

Thank you very much! Now I can finally finish up my project! Huzzah! :D
 

Users who are viewing this thread

Back
Top Bottom