SQL GROUP statement

  • 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));

  • 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

    “Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw

    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

  • 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?

    Brandie Tarvin, MCITP Database AdministratorLiveJournal Blog: http://brandietarvin.livejournal.com/[/url]On LinkedIn!, Google+, and Twitter.Freelance Writer: ShadowrunLatchkeys: Nevermore, Latchkeys: The Bootleg War, and Latchkeys: Roscoes in the Night are now available on Nook and Kindle.

  • 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!!

  • 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.

    Brandie Tarvin, MCITP Database AdministratorLiveJournal Blog: http://brandietarvin.livejournal.com/[/url]On LinkedIn!, Google+, and Twitter.Freelance Writer: ShadowrunLatchkeys: Nevermore, Latchkeys: The Bootleg War, and Latchkeys: Roscoes in the Night are now available on Nook and Kindle.

  • 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!

    “Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw

    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

  • 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.

    Brandie Tarvin, MCITP Database AdministratorLiveJournal Blog: http://brandietarvin.livejournal.com/[/url]On LinkedIn!, Google+, and Twitter.Freelance Writer: ShadowrunLatchkeys: Nevermore, Latchkeys: The Bootleg War, and Latchkeys: Roscoes in the Night are now available on Nook and Kindle.

  • 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)

  • 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!

    “Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw

    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

  • 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;

    Brandie Tarvin, MCITP Database AdministratorLiveJournal Blog: http://brandietarvin.livejournal.com/[/url]On LinkedIn!, Google+, and Twitter.Freelance Writer: ShadowrunLatchkeys: Nevermore, Latchkeys: The Bootleg War, and Latchkeys: Roscoes in the Night are now available on Nook and Kindle.

  • 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)

  • 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? 😎

    “Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw

    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

  • 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.

    Brandie Tarvin, MCITP Database AdministratorLiveJournal Blog: http://brandietarvin.livejournal.com/[/url]On LinkedIn!, Google+, and Twitter.Freelance Writer: ShadowrunLatchkeys: Nevermore, Latchkeys: The Bootleg War, and Latchkeys: Roscoes in the Night are now available on Nook and Kindle.

Viewing 13 posts - 1 through 13 (of 13 total)

You must be logged in to reply to this topic. Login to reply