[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now

x
?
Solved

Group into exempt and non-exempt.

Posted on 2006-04-02
4
Medium Priority
?
746 Views
Last Modified: 2011-10-03
My question is how to Select the employees’ last names and group them by salary within job titles that are grouped into exempt and non-exempt.  

Employee
      Social_security_number      char
      Last_name                  varchar
      First_name                  varchar
      Address                  varchar
      City                        varchar
      State                  char
      Zip_code                  char
        Telephone_area_code      char            
      Telephone_number            char      
      Email_address            varchar      
      Job_title_code            varchar      
      Hire_date                  smalldatetime
      Salary                  money
                        
Job_title
      Job_title_code            varchar
      Job_title                  varchar
      Exempt_non_exempt_status  bit         (1-Exempt, 0-non-Exempt)      
      Minimum_salary            money
      Maximum_salary            money






0
Comment
Question by:mayan1
  • 2
4 Comments
 
LVL 75

Accepted Solution

by:
Aneesh Retnakaran earned 200 total points
ID: 16357184
SELECT E.Last_Name, JT. Job_title_code , JT.Exempt_non_Exempt_status
FROM Employee E
Inner Join Job_Title
ON E. Job_title_code =JT. Job_title_code

GROUP BY E.Last_Name,JT. Job_title_code , JT.Exempt_non_Exempt_status
0
 
LVL 10

Expert Comment

by:StephenCairns
ID: 16357188

order by Job_title,  Exempt_non_exempt_status, last names ,  salary
0
 
LVL 75

Expert Comment

by:Aneesh Retnakaran
ID: 16357199
I forgot to put the aliase


SELECT E.Last_Name, JT. Job_title_code , JT.Exempt_non_Exempt_status
FROM Employee E
Inner Join Job_Title JT
ON E. Job_title_code =JT. Job_title_code

GROUP BY E.Last_Name,JT. Job_title_code , JT.Exempt_non_Exempt_status
0
 
LVL 50

Expert Comment

by:Lowfatspread
ID: 16358626
SELECT case exempt_non_exempt_status when 1 then 'Exempt' else 'Non Exempt' end as [Status]
          , JT. Job_title
          , Salary
           ,Last_Name
FROM Employee E
Inner Join Job_Title JT
ON E. Job_title_code =JT. Job_title_code

order by 1,Job_title, Salary Desc,last_name

i think you are using the phrase GROUP BY by to indicate the ORDER in which you want the rows returned....
beware in SQL GROUP BY is used to indicate a summation/totaling activity and the ORDER BY clause is used
to determine the actual result order...

hth
0

Featured Post

Free Tool: IP Lookup

Get more info about an IP address or domain name, such as organization, abuse contacts and geolocation.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

In the first part of this tutorial we will cover the prerequisites for installing SQL Server vNext on Linux.
Microsoft Access has a limit of 255 columns in a single table; SQL Server allows tables with over 255 columns, but reading that data is not necessarily simple.  The final solution for this task involved creating a custom text parser and then reading…
This video shows, step by step, how to configure Oracle Heterogeneous Services via the Generic Gateway Agent in order to make a connection from an Oracle session and access a remote SQL Server database table.
Using examples as well as descriptions, and references to Books Online, show the documentation available for date manipulation functions and by using a select few of these functions, show how date based data can be manipulated with these functions.
Suggested Courses

873 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question