November 13, 2009 at 8:30 am
I'm a student in an online course and advice must cost extra, because I'm not getting it. I'm using SQl Server 2008 Developer.
I have to pull data from 2 tables and AVG salaries by EEO Classification. (Employee table contains Salary and EEOClasses table contains the ClassTitle) Every example I've found in my textbook and online only uses data from 1 table.
This is what I get returned:
[font="Courier New"]
CraftWorkerSkilled25900.8333
OfficeClerical 25900.8333
OfficialsManagers 25900.8333
OperativeSemiSkilled25900.8333
SalesWorker 25900.8333
Technician 25900.8333[/font]
This is my query, what am I doing wrong?
[font="Courier New"]SELECT ClassTitle, AVG(Salary)
FROM EEOClasses, Employee
GROUP BY ClassTitle[/font]
November 13, 2009 at 9:00 am
First of all the query you have posted is a cross join which creates a Cartesian product. You need to specify the column(s) you are joining the 2 tables. Here's an example of what I think you want, but without the table definitions I can't get the column names correct:
SELECT
EC.ClassTitle,
AVG(E.Salary)
FROM
EEOClasses AS EC JOIN
Employee AS E ON
EC.ClassId = E.ClassId
GROUP BY
EC.ClassTitle
Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question
November 13, 2009 at 9:20 am
It worked! Thank you so much!!!
Would the same query structure (JOIN) be used when doing a COUNT on 2 tables?
[font="Comic Sans MS"]"Select the count of employees’ and group them by salary within their job title."[/font]
Is WITHIN their job title different than grouping them BY their job title? I'm not stupid - really. I'm a Web Graphics major forced to take this class.
November 13, 2009 at 9:35 am
Yes, whenever you are returning data from more than 1 table you need to JOIN the tables.
Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question
November 13, 2009 at 9:36 am
Thanks for your help.
November 16, 2009 at 1:23 am
i think in this case the right inner join but if you need the cartasian product of the 2 table you have to use the select from 2 table select statment
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply