March 23, 2009 at 3:22 am
Using the Employees table below how would you select by Last_name and group by Salary within EE01_Classification?
I thought this would work but no dice thus far:
Select Last_name
FROM Employees
GROUP BY Salary
CREATE TABLE Employees (
Emp_ID CHAR(6) NOT NULL PRIMARY KEY,
Last_name VARCHAR(20) NOT NULL,
First_name VARCHAR(20) NULL,
Address VARCHAR(40) NULL,
City VARCHAR(32) NULL,
State CHAR(2) NULL,
Telephone_area_code VARCHAR(3) NULL,
Telephone_number VARCHAR(16) NULL,
EE01_Classification VARCHAR(60) NULL,
Hire_date DATETIME NULL,
Salary MONEY NULL,
Gender NCHAR(1) NOT NULL,
Age VARCHAR(3) NULL,
Job_Title VARCHAR(50) NOT NULL references Job(Job_Title));
March 23, 2009 at 3:31 am
munderhill73525 (3/23/2009)
Using the Employees table below how would you select by Last_name and group by Salary within EE01_Classification?
If you group by salary alone, you're saying "return one row per salary value", so if there's more than one last_name with the same salary value, you would return only one of them. You can of course do this using MIN(Last_name) or MAX(Last_name), but this probably isn't what you want 🙂
How about setting up some sample data along with a table creation script (with only the columns required for the exercise), and a table of expected output? Chances are, by the time you've done this, you will probably have figured it out yourself! The link below will show you how to set up readily-consumed sample data.
Cheers
ChrisM
For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden
March 23, 2009 at 4:24 am
munderhill73525 (3/23/2009)
Using the Employees table below how would you select by Last_name and group by Salary within EE01_Classification?I thought this would work but no dice thus far:
Select Last_name
FROM Employees
GROUP BY Salary
When you say "no dice", what do you mean? What results are you getting that it doesn't seem to be working?
The query looks fine to me. Though, as Chris said, if you've got more than one salary per name, or more than one employee with the same last name and they all have different salaries, you're going to get different results.
IE,
First Last Salary
John Smith 25000.00
Lisa Smith 35000.00
Debbie Smith 42000.00
will get you
Smith 25000.00
Smith 35000.00
Smith 42000.00
Even if you put a DISTINCT clause in, you'll be getting these values. And if John has 25000.00 and 30000.00, you'll be getting to values for him.
So again, what is your expected result set?
March 24, 2009 at 7:03 am
I think I figured this one out!!
Select Last_name,Salary,EE01_Classification
FROM Employees
WHERE Salary IN (SELECT Salary
FROM Employees
GROUP BY EE01_Classification,Salary)
ORDER BY EE01_Classification;
It seems to work....
Now on to the next one...GROUP BY with multiple tables...uggghhhhhh
Thanks for your help!!
March 24, 2009 at 7:34 am
You're welcome. Though, I'm not sure we helped much.
You might want to make very very sure your result set matches the expected data before you cheer.
March 24, 2009 at 7:42 am
Select Last_name,Salary,EE01_Classification
FROM Employees
WHERE Salary IN (SELECT Salary
FROM Employees
GROUP BY EE01_Classification,Salary)
ORDER BY EE01_Classification;
This means:
Select Last_name,Salary,EE01_Classification
FROM Employees
WHERE Salary IN (all employee salaries)
ORDER BY EE01_Classification;
Which is the same as:
Select Last_name,Salary,EE01_Classification
FROM Employees
WHERE 1 = 1 -- unrestricted
ORDER BY EE01_Classification;
If this is what you want, then great - but it probably isn't!
For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden
March 24, 2009 at 7:47 am
I have to agree with Chris. I really don't think that query will get you the results you want.
But you'd have to post sample data and expected results for us to be sure.
March 24, 2009 at 7:51 am
Drop table Employees;
Drop table Job;
CREATE TABLE Job (
EEO_Classification VARCHAR(60) NOT NULL,
Job_Title VARCHAR(50) NOT NULL PRIMARY KEY,
Job_description VARCHAR(1000) NULL,
Exemption_Status VARCHAR(20) NOT NULL );
CREATE TABLE Employees (
Emp_ID CHAR(6) NOT NULL PRIMARY KEY,
Last_name VARCHAR(20) NOT NULL,
First_name VARCHAR(20) NULL,
Address VARCHAR(40) NULL,
City VARCHAR(32) NULL,
State CHAR(2) NULL,
Telephone_area_code VARCHAR(3) NULL,
Telephone_number VARCHAR(16) NULL,
EE01_Classification VARCHAR(60) NULL,
Hire_date DATETIME NULL,
Salary MONEY NULL,
Gender NCHAR(1) NOT NULL,
Age VARCHAR(3) NULL,
Job_Title VARCHAR(50) NOT NULL references Job(Job_Title));
INSERT INTO Job (EEO_Classification, Job_Title, Job_description, Exemption_Status)
SELECT 'Office/Clerical','Accounting Clerk','Computes, classifies, records, and
verifies numerical data for use in maintaining accounting records','Non-exempt'
UNION ALL
SELECT 'Officials & Managers','Asst. Manager','Supervises and coordinates activities
of workers in department of food store. Assists store manager in daily operations of
store','Exempt'
UNION ALL
SELECT 'Sales Workers','Bagger','Places customer orders in bags. Performs carryout
duties for customers','Non-exempt'
UNION ALL
SELECT 'Sales Workers','Cashier','Operates cash register to itemize and total
customers purchases in grocery store.','Non-exempt'
UNION ALL
SELECT 'Technician','Computer Support Specialist','Installs, modifies, and makes minor
repairs to personal computer hardware and software systems and provides technical
assistance and training to system users','Non-exempt'
UNION ALL
SELECT 'Officials & Managers','Director of Finance & Accounting','Plans and directs the
finance and accounting activities for Kudler Fine Foods.','Exempt'
UNION ALL
SELECT 'Craft Workers(skilled)','Retail Asst. Bakery & Pastry','Obtains or prepares
food items requested by customers in retail food store.','Non-exempt'
UNION ALL
SELECT 'Operatives(semi-skilled)','Retail Asst. Butchers and Seafood Specialists','Obtains
or prepares food items requested by customers in retail food store.','Non-exempt'
UNION ALL
SELECT 'Office/Clerical','Stocker','Stores, prices, and restocks merchandise displays
in store','Non-exempt'
UNION ALL
SELECT 'Operatives(skilled)','Butcher','Cuts meat','Non-exempt';
INSERT INTO Employees (Emp_ID, Last_name, First_name, Address,
City, State, Telephone_area_code, Telephone_number, EE01_Classification, Hire_date,
Salary, Gender, Age, Job_Title)
SELECT '800','Edelman','Glenn','175 Bishops Lane','La Jolla','CA',619,'555-0199',
'Sales Workers','10-07-2003',21500,'M',64,'Cashier'
UNION ALL
SELECT '801','McMullen','Eric','763 Church Street','Lemongrove','CA',619,'555-0133',
'Sales Workers','11-01-2002',13500,'M',20,'Bagger'
UNION ALL
SELECT '802','Slentz','Raj','123 Torrey Drive','North Clairmont','CA',619,'555-1023',
'Officials & Managers','06-01-2000',48000,'M',34,'Asst. Manager'
UNION ALL
SELECT '803','Broun','Erin','2045 Parkway Apt2B','Encinitas','CA',760,'555-0100',
'Sales Workers','03-12-2003',10530,'F',24,'Bagger'
UNION ALL
SELECT '804','Carpenter','Donald','927 Second St','Encinitas','CA',619,'555-0154',
'Office/Clerical','11-01-2003',15000,'M',18,'Stocker'
UNION ALL
SELECT '805','Esquivez','David','10983 North Coast Hwy Apt 902','Encinitas','CA',760,
'555-0108','Operatives(semi skilled)','07-25-2003',18500,'M',25,'Butcher'
UNION ALL
SELECT '806','Sharp','Nancy','10793 Montecino Road','Ramona','CA',858,'555-0135',
'Sales Workers','07-12-2003',21000,'F',24,'Cashier'
UNION ALL
SELECT '807','McNamara','Juanita','923 Parkway Highway',NULL,'CA',619,'555-0206',
'Office/Clerical','10-29-1999',25500,'F',32,'Accounting Clerk'
UNION ALL
SELECT '808','Nguyen','Meredith','10583 Arenas Street','La Jolla','CA',619,'555-0102',
'Technician','09-27-1998',43000,'F',25,'Computer Support Specialist'
UNION ALL
SELECT '809','Stephens','Harvey','7863 High Bluff Drive','La Jolla','CA',619,'555-0123',
'Officials & Managers','03-01-1998',75000,'M',51,'Director of Finance & Accounting'
UNION ALL
SELECT '810','Vu','Matthew','981 Torrey Pines Road','La Jolla','CA',619,'555-0138',
'Technician','08-16-2000',37000,'M',26,'Computer Support Specialist'
UNION ALL
SELECT '811','Avery','Ledonna','198 Governor Drive','Del Mar','CA',619,'555-0135',
'Craft Workers(skilled)','03-28-2003',21000,'F',23,'Retail Asst. Bakery & Pastry'
UNION ALL
SELECT '812','Drohos','Craig',NULL,'Selano Beach','CA',619,'555-0202',
'Officials & Managers','06-15-2000',51000,'M',32,'Asst. Manager'
UNION ALL
SELECT '813','Meier','Elaine','9703 Dronid Lane','Del Mar','CA',858,'555-0112',
'Sales Workers','09-10-2000',20500,'F',51,'Cashier'
UNION ALL
SELECT '814','Quillian','Stanley','98542 Wandering Road Apt2B','Del Mar','CA',760,'555-0198',
'Operatives(semi skilled)','12-16-1999',23000,'M',29,'Retail Asst. Butchers and Seafood Specialists'
UNION ALL
SELECT '815','Tyink','Thomas','87592 Pacific Heights Blvd.','Del Mar','CA',858,'555-0159',
'Craft Workers(skilled)','05-01-2001',19000,'M',32,'Retail Asst. Bakery & Pastry'
UNION ALL
SELECT '816','Vance','Brent','927 Cynthia Lane','Poway','CA',858,'555-0157',
'Sales Workers','03-29-2001',10530,'M',22,'Bagger';
Select Last_name,Salary,EE01_Classification
FROM Employees
WHERE Salary IN (SELECT Salary
FROM Employees
GROUP BY EE01_Classification,Salary)
ORDER BY EE01_Classification;
Honestly...I'm not sure what the results are supposed to be. I do know that this query groups the employees into their classifications and displays the salary along with it. It could be wrong for all I know?!?! It "looks" right.....
Thanks again for your help on this!! This SQL stuff is tough on the brain :o)
March 24, 2009 at 8:06 am
It seems you're confusing "group the employees together by some attribute" (which you would achieve with ORDER BY) with sql GROUP BY, which is something quite different - usually used for obtaining summed figures across an attribute. Anyway, if the requirements become more clear, there's now sample data to play with. Thanks!
For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden
March 24, 2009 at 8:08 am
Okay, let's start with the first question.
What do you want to do (in English) with the results. Forget about your original post. What is the intent of gathering this data? Did you decide you wanted to do this or did someone else request a report?
Because just looking at the sample data, you don't need a group by. The following code works just fine:
Select Last_name,Salary,EE01_Classification
FROM Employees
ORDER BY EE01_Classification;
March 24, 2009 at 8:12 am
The "root" of my problem is the assignment itself. It specifically states to use GROUP BY statements to accomplish the scenarios which in turn has me TOTALLY confused!! The exact assignment reads as follows:
Using the updated database, write the following queries using the SQL GROUP statement:
· Group employees by job classification: Select the employees’ last names and group them by EEO_1_Classification.
· Group employees by salary: Select the employees’ last names and group them by salary.
· Group employees by salary within their job classification: Select the employees’ last names and group them by salary within their EEO-1 Classification.
· Select the employees’ last names and group them by salary within job titles that are grouped into exempt and non-exempt.
The last one is also giving me a fit!!
I do not think it makes sense either...but if the assignment calls for it, what do I do?!?! :o)
March 24, 2009 at 8:22 am
munderhill73525 (3/24/2009)
The "root" of my problem is the assignment itself. It specifically states to use GROUP BY statements to accomplish the scenarios which in turn has me TOTALLY confused!! The exact assignment reads as follows:Using the updated database, write the following queries using the SQL GROUP statement:
· Group employees by job classification: Select the employees’ last names and group them by EEO_1_Classification.
· Group employees by salary: Select the employees’ last names and group them by salary.
· Group employees by salary within their job classification: Select the employees’ last names and group them by salary within their EEO-1 Classification.
· Select the employees’ last names and group them by salary within job titles that are grouped into exempt and non-exempt.
The last one is also giving me a fit!!
I do not think it makes sense either...but if the assignment calls for it, what do I do?!?! :o)
This is quite a lot of homework, for TSQL101!
Brandie, got any porkchops? 😎
For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden
March 24, 2009 at 10:44 am
Ahh. I did not realize this was a homework assignment. That changes things, unfortunately. We don't answer homework assignments or interview questions on this board.
What I can do is give you a piece of advice. Break down each assignment section by pieces.
"Group employees by salary within their job classification: Select the employees’ last names and group them by salary within their EEO-1 Classification."
So, you know that you have to Select the last name, the classification and the salary. You also know that you have to group them "by salary". But if you look at the requirements a little more carefully, you'll see there's technically 2 groupings, which you did have in part of your earlier query. While I don't agree your earlier query is the way to go, you still have a couple of options. Doing a three-way grouping or using a subquery in a place other than the WHERE clause are the first two that come to mind.
Yes, I'm being vague too. I suggest you use Books Online to look up GROUP BY and see what MS has to say about the whole thing.
And if you still can't figure it out by reading, try to diagram it out on a piece of paper. See if that helps.
Viewing 13 posts - 1 through 13 (of 13 total)
You must be logged in to reply to this topic. Login to reply