[Last Call] Learn about multicloud storage options and how to improve your company's cloud strategy. Register Now

x
?
Solved

SQL Group By

Posted on 2006-07-18
14
Medium Priority
?
1,387 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 200 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
Veeam Task Manager for Hyper-V

Task Manager for Hyper-V provides critical information that allows you to monitor Hyper-V performance by displaying real-time views of CPU and memory at the individual VM-level, so you can quickly identify which VMs are using host resources.

 
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 93

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 93

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

Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

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

Ever needed a SQL 2008 Database replicated/mirrored/log shipped on another server but you can't take the downtime inflicted by initial snapshot or disconnect while T-logs are restored or mirror applied? You can use SQL Server Initialize from Backup…
This month, Experts Exchange sat down with resident SQL expert, Jim Horn, for an in-depth look into the makings of a successful career in SQL.
Via a live example combined with referencing Books Online, show some of the information that can be extracted from the Catalog Views in SQL Server.
Viewers will learn how to use the SELECT statement in SQL and will be exposed to the many uses the SELECT statement has.

650 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