November 22, 2011 at 10:01 pm
Hello everyone,
I'm trying to run a couple queries, but am having a difficult time linking the two tables for the query. Here are the details of the database and what I am trying to accomplish.
Table1: Employee
Primary Key: ID
Foreign Key: Job_Title_Code
Table2: Job_Title
Primary Key: Job_Title_Code
First query I am trying to accomplish is to COUNT the total number of employees and list them by their "salary" within each "job title". I tried the following code, but keep getting a Msg 245, Level 16, State 1, Line 1 error because it cannot convert the varchar value "Director of IT" to datatype "int". The "Salary" column is in the "Employee" table and the "Job Title" column is in the "Job_Title" table.
GO
SELECT Salary, COUNT(*) as "Total Employees"
FROM Employee
JOIN Job_Title ON Employee.Job_Title_Code=Job_Title.Title
GROUP BY Salary
The second query I am trying to do is show the total number of employees for each salary grouped by exempt status. The "Salary" column is in the "Employee" table and the "Exempt" column is in the "Job_Title" table.
Please help me. I'm about ready to pull my hair out, lol.
November 22, 2011 at 10:16 pm
Getting a varchar() to int conversion failure is kind of strange here. Any chance you can post the DDL to the two tables?
Never stop learning, even if it hurts. Ego bruises are practically mandatory as you learn unless you've never risked enough to make a mistake.
For better assistance in answering your questions[/url] | Forum Netiquette
For index/tuning help, follow these directions.[/url] |Tally Tables[/url]
Twitter: @AnyWayDBA
November 22, 2011 at 11:37 pm
Hi... What is the datatype of each field? especially the Job_Title field in Employee and Job_Title tables..
November 23, 2011 at 2:29 am
I think you were joining on the wrong column (title instead of Job_Title_Code)
SELECT Salary, COUNT(*) as "Total Employees"
FROM Employee
JOIN Job_Title ON Employee.Job_Title_Code=Job_Title.Job_Title_Code
GROUP BY Salary
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply