Solved

SQL Group By

Posted on 2006-07-18
14
1,362 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
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
 
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
Windows Server 2016: All you need to know

Learn about Hyper-V features that increase functionality and usability of Microsoft Windows Server 2016. Also, throughout this eBook, you’ll find some basic PowerShell examples that will help you leverage the scripts in your environments!

 

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

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
sql server query from excel 3 57
SQL server is using more virtual memory. 5 68
ms sql last 8 weeks as columns 5 28
Sql Query with datetime 3 12
Introduction SQL Server Integration Services can read XML files, that’s known by every BI developer.  (If you didn’t, don’t worry, I’m aiming this article at newcomers as well.) But how far can you go?  When does the XML Source component become …
For both online and offline retail, the cross-channel business is the most recent pattern in the B2C trade space.
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.
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

895 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

18 Experts available now in Live!

Get 1:1 Help Now