We help IT Professionals succeed at work.

We've partnered with Certified Experts, Carl Webster and Richard Faulkner, to bring you two Citrix podcasts. Learn about 2020 trends and get answers to your biggest Citrix questions!Listen Now

x

Group into exempt and non-exempt.

mayan1
mayan1 asked
on
Medium Priority
765 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






Comment
Watch Question

Database Consultant
CERTIFIED EXPERT
Top Expert 2009
Commented:
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

Not the solution you were looking for? Getting a personalized solution is easy.

Ask the Experts

order by Job_title,  Exempt_non_exempt_status, last names ,  salary
AneeshDatabase Consultant
CERTIFIED EXPERT
Top Expert 2009

Commented:
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
CERTIFIED EXPERT
Top Expert 2011

Commented:
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
Access more of Experts Exchange with a free account
Thanks for using Experts Exchange.

Create a free account to continue.

Limited access with a free account allows you to:

  • View three pieces of content (articles, solutions, posts, and videos)
  • Ask the experts questions (counted toward content limit)
  • Customize your dashboard and profile

*This site is protected by reCAPTCHA and the Google Privacy Policy and Terms of Service apply.

OR

Please enter a first name

Please enter a last name

8+ characters (letters, numbers, and a symbol)

By clicking, you agree to the Terms of Use and Privacy Policy.