Solved

SQL Group By

Posted on 2006-07-18
14
1,373 Views
Last Modified: 2008-01-09
Trying to group employees by salary by selecting the employees’ last names and grouping them by their salary.  The following is my SQL query analyzer command:

SELECT Employee_number, First_name, Last_name, Address, City, State, Telephone_area_code, Telephone_number, EEO_Classification, Hire_date, Salary, Gender, Race, Age
FROM employee
GROUP BY salary

I receive the following errors:
Server: Msg 8120, Level 16, State 1, Line 1
Column 'employee.Employee_number' is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause.
Server: Msg 8120, Level 16, State 1, Line 1
Column 'employee.First_name' is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause.
Server: Msg 8120, Level 16, State 1, Line 1
Column 'employee.Last_name' is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause.
Server: Msg 8120, Level 16, State 1, Line 1
Column 'employee.Address' is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause.
Server: Msg 8120, Level 16, State 1, Line 1
Column 'employee.City' is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause.
Server: Msg 8120, Level 16, State 1, Line 1
Column 'employee.State' is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause.
Server: Msg 8120, Level 16, State 1, Line 1
Column 'employee.Telephone_area_code' is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause.
Server: Msg 8120, Level 16, State 1, Line 1
Column 'employee.Telephone_number' is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause.
Server: Msg 8120, Level 16, State 1, Line 1
Column 'employee.EEO_Classification' is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause.
Server: Msg 8120, Level 16, State 1, Line 1
Column 'employee.Hire_date' is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause.
Server: Msg 8120, Level 16, State 1, Line 1
Column 'employee.Gender' is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause.
Server: Msg 8120, Level 16, State 1, Line 1
Column 'employee.Race' is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause.
Server: Msg 8120, Level 16, State 1, Line 1
Column 'employee.Age' is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause.

I tried to run this in multiple forms according to online help but no luck.  Is there something simple I am missing?

Thank you.




0
Comment
Question by:JMartin32
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 4
  • 4
  • 2
  • +1
14 Comments
 
LVL 25

Accepted Solution

by:
Mr_Peerapol earned 50 total points
ID: 17135447
I guess what you need is ORDER BY.

SELECT Employee_number, First_name, Last_name, Address, City, State, Telephone_area_code, Telephone_number, EEO_Classification, Hire_date, Salary, Gender, Race, Age
FROM employee
ORDER BY salary
0
 
LVL 75

Expert Comment

by:Aneesh Retnakaran
ID: 17135450
JMartin32,
> GROUP BY salary

I think you need ORDER BY

replace GROUP BY salary  with

ORDER BY Last_Name, Salary
0
 

Author Comment

by:JMartin32
ID: 17135477


If I wanted to expand this query and group employees by salary within their job classification by selecting the employees’ last names and group them by salary within their EEO-1 Classification would this require only additional parameters to the ORDER BY clause?

Thanks.
0
Online Training Solution

Drastically shorten your training time with WalkMe's advanced online training solution that Guides your trainees to action. Forget about retraining and skyrocket knowledge retention rates.

 
LVL 25

Expert Comment

by:Mr_Peerapol
ID: 17135483
ORDERY EEO_Classification, Salary
0
 
LVL 25

Expert Comment

by:Mr_Peerapol
ID: 17135486
ORDER BY EEO_Classification, Salary
0
 
LVL 92

Expert Comment

by:Patrick Matthews
ID: 17135490
Hi JMartin32,

Yes, use the ORDER BY clause to sort the data.  By default, SQL Server sorts ascendingly; to sort descendingly:

ORDER BY Salary DESC, Last_Name

That sorts Salary in descending order, and within Salary, sorts Last_Name in ascending order.

Regards,

Patrick
0
 

Author Comment

by:JMartin32
ID: 17135533
Patrick,

Thank you.
0
 

Author Comment

by:JMartin32
ID: 17141480
Patrick,

Thought about this some more since resolving yesterday.  What would it take to use the Group By clause in this example?

Thanks.
0
 

Author Comment

by:JMartin32
ID: 17141791
Anyone can respond, forgot about the setup.

Thanks
0
 
LVL 92

Expert Comment

by:Patrick Matthews
ID: 17143124
There is no reason to use GROUP BY, as you are not aggregating.

Regards,

Patrick
0
 
LVL 25

Expert Comment

by:Mr_Peerapol
ID: 17597870
I guess I posted the correct answer.
0
 
LVL 75

Expert Comment

by:Aneesh Retnakaran
ID: 17598163
I too agree with Mr_peerapol, he posted ahead me may be by a few seconds..
0

Featured Post

Migrating Your Company's PCs

To keep pace with competitors, businesses must keep employees productive, and that means providing them with the latest technology. This document provides the tips and tricks you need to help you migrate an outdated PC fleet to new desktops, laptops, and tablets.

Question has a verified solution.

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

Suggested Solutions

International Data Corporation (IDC) prognosticates that before the current the year gets over disbursing on IT framework products to be sent in cloud environs will be $37.1B.
Ever wondered why sometimes your SQL Server is slow or unresponsive with connections spiking up but by the time you go in, all is well? The following article will show you how to install and configure a SQL job that will send you email alerts includ…
Using examples as well as descriptions, and references to Books Online, show the different Recovery Models available in SQL Server and explain, as well as show how full, differential and transaction log backups are performed
Viewers will learn how the fundamental information of how to create a table.

730 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