Solved

SQL Group By

Posted on 2006-07-18
14
1,359 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
  • 4
  • 4
  • 2
  • +1
14 Comments
 
LVL 25

Accepted Solution

by:
Mr_Peerapol earned 50 total points
Comment Utility
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
Comment Utility
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
Comment Utility


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
 
LVL 25

Expert Comment

by:Mr_Peerapol
Comment Utility
ORDERY EEO_Classification, Salary
0
 
LVL 25

Expert Comment

by:Mr_Peerapol
Comment Utility
ORDER BY EEO_Classification, Salary
0
 
LVL 92

Expert Comment

by:Patrick Matthews
Comment Utility
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
Free Gift Card with Acronis Backup Purchase!

Backup any data in any location: local and remote systems, physical and virtual servers, private and public clouds, Macs and PCs, tablets and mobile devices, & more! For limited time only, buy any Acronis backup products and get a FREE Amazon/Best Buy gift card worth up to $200!

 

Author Comment

by:JMartin32
Comment Utility
Patrick,

Thank you.
0
 

Author Comment

by:JMartin32
Comment Utility
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
Comment Utility
Anyone can respond, forgot about the setup.

Thanks
0
 
LVL 92

Expert Comment

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

Regards,

Patrick
0
 
LVL 25

Expert Comment

by:Mr_Peerapol
Comment Utility
I guess I posted the correct answer.
0
 
LVL 75

Expert Comment

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

Featured Post

Free Gift Card with Acronis Backup Purchase!

Backup any data in any location: local and remote systems, physical and virtual servers, private and public clouds, Macs and PCs, tablets and mobile devices, & more! For limited time only, buy any Acronis backup products and get a FREE Amazon/Best Buy gift card worth up to $200!

Join & Write a Comment

Nowadays, some of developer are too much worried about data. Who is using data, who is updating it etc. etc. Because, data is more costlier in term of money and information. So security of data is focusing concern in days. Lets' understand the Au…
The Delta outage: 650 cancelled flights, more than 1200 delayed flights, thousands of frustrated customers, tens of millions of dollars in damages – plus untold reputational damage to one of the world’s most trusted airlines. All due to a catastroph…
Familiarize people with the process of retrieving data from SQL Server using an Access pass-thru query. Microsoft Access is a very powerful client/server development tool. One of the ways that you can retrieve data from a SQL Server is by using a pa…
Via a live example, show how to shrink a transaction log file down to a reasonable size.

762 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

Need Help in Real-Time?

Connect with top rated Experts

7 Experts available now in Live!

Get 1:1 Help Now