?
Solved

SQL Group By

Posted on 2006-07-18
14
Medium Priority
?
1,383 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
Visualize your virtual and backup environments

Create well-organized and polished visualizations of your virtual and backup environments when planning VMware vSphere, Microsoft Hyper-V or Veeam deployments. It helps you to gain better visibility and valuable business insights.

 
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

Get real performance insights from real users

Key features:
- Total Pages Views and Load times
- Top Pages Viewed and Load Times
- Real Time Site Page Build Performance
- Users’ Browser and Platform Performance
- Geographic User Breakdown
- And more

Question has a verified solution.

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

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.
It is possible to export the data of a SQL Table in SSMS and generate INSERT statements. It's neatly tucked away in the generate scripts option of a database.
This video shows, step by step, how to configure Oracle Heterogeneous Services via the Generic Gateway Agent in order to make a connection from an Oracle session and access a remote SQL Server database table.
Via a live example, show how to extract insert data into a SQL Server database table using the Import/Export option and Bulk Insert.
Suggested Courses

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