GROUP - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

GROUP

Author

Topic

kprofgold
Starting Member

15 Posts

Posted - 2011-10-16 : 01:47:35

So here I am stuck again but this time on GROUP. Here is what I am trying to accomplish:Select employees’ last names and group them by EmployerInformationReport. Now if I'm not mistaken I do need a join in here as well because the EmployerInformationReport is on a different table than the LastNames. I just can't figure out why the query is unsuccessful.

USE KudlerFineFoods
SELECT LastName FROM Employee
INNER JOIN dbo.JobTitle ON
JobTitle.JobID = Employee.JobID
GROUP BY JobTitle.EmployerInformationReport
GO

GilaMonster
Master Smack Fu Yak Hacker

4507 Posts

Posted - 2011-10-16 : 02:27:30

Without the error messages or a description of what it does that it shouldn't, we're also going to have trouble figuring out why the query is unsuccessful.

One thing that looks odd, is that there's no aggregate. A group by is for aggregation - sum, average, min, max. You have no aggregation, so what are you using a group by for?

--
Gail Shaw
SQL Server MVP

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-10-16 : 02:36:40

quote:
Originally posted by kprofgold

So here I am stuck again but this time on GROUP. Here is what I am trying to accomplish:Select employees’ last names and group them by EmployerInformationReport. Now if I'm not mistaken I do need a join in here as well because the EmployerInformationReport is on a different table than the LastNames. I just can't figure out why the query is unsuccessful.

USE KudlerFineFoods
SELECT LastName FROM Employee
INNER JOIN dbo.JobTitle ON
JobTitle.JobID = Employee.JobID
GROUP BY JobTitle.EmployerInformationReport
GO

the requirement itself doesnt make sense to me. last name would be different on most of occasions so didnt understand the use of grouping by it. anyways here's solution


USE KudlerFineFoods
SELECT JobTitle.EmployerInformationReport,LastName FROM Employee
INNER JOIN dbo.JobTitle ON
	JobTitle.JobID = Employee.JobID
GROUP BY JobTitle.EmployerInformationReport,Employee.LastName
GO

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-10-16 : 02:36:41

quote:
Originally posted by kprofgold

So here I am stuck again but this time on GROUP. Here is what I am trying to accomplish:Select employees’ last names and group them by EmployerInformationReport. Now if I'm not mistaken I do need a join in here as well because the EmployerInformationReport is on a different table than the LastNames. I just can't figure out why the query is unsuccessful.

USE KudlerFineFoods
SELECT LastName FROM Employee
INNER JOIN dbo.JobTitle ON
JobTitle.JobID = Employee.JobID
GROUP BY JobTitle.EmployerInformationReport
GO

the requirement itself doesnt make sense to me. last name would be different on most of occasions so didnt understand the use of grouping by it. anyways here's solution


USE KudlerFineFoods
SELECT JobTitle.EmployerInformationReport,Employee.LastName FROM Employee
INNER JOIN dbo.JobTitle ON
	JobTitle.JobID = Employee.JobID
GROUP BY JobTitle.EmployerInformationReport,Employee.LastName
GO

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

kprofgold
Starting Member

15 Posts

Posted - 2011-10-16 : 02:37:57

Here is my error message:Msg 8120, Level 16, State 1, Line 2
Column 'Employee.LastName' is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause. I'm trying to group the employees last names by their Employee Classification Report. I did read some on the aggregation in my book but the examples were not very easy to understand.

kprofgold
Starting Member

15 Posts

Posted - 2011-10-16 : 02:44:16

Thank you... I have a few more Ill see if I can get them... Honestly I'm not sure why they ask for these.. I'm guessing so we at least get an idea of how they work... Hardest part about it is we don't really learn about the statements in class until the week after the assignment is due. Kind of backwards in my eyes. Thanks again for the help. Hopefully I can figure the rest of it out.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-10-16 : 02:44:32

quote:
Originally posted by kprofgold

Here is my error message:Msg 8120, Level 16, State 1, Line 2
Column 'Employee.LastName' is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause. I'm trying to group the employees last names by their Employee Classification Report. I did read some on the aggregation in my book but the examples were not very easy to understand.

the reason was you didnt include it in GROUP BY. See my modified suggestion it includes lastname also in GROUP. When you apply group by you need to either include the column in group if you want to return all values in it or apply aggregation. This is because when you apply group by on a column (in your example EmployerInformationReport) you're effectively asking query to return you one record per each value of EmployerInformationReport. This would have multiple values of LastName involved in each value of EmployerInformationReport so query engine doesnt know which of it needs to return as you can return only a single value. thats why it mandates you to apply any aggregation function like MIN(),MAX() etc. If you're including Lastname also in group by then what you're asking query engine is return record per combination of EmployerInformationReport,LastName and then it will able to return them without any ambiguity.

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

GilaMonster
Master Smack Fu Yak Hacker

4507 Posts

Posted - 2011-10-16 : 02:47:38

quote:
Originally posted by kprofgold

I'm trying to group the employees last names by their Employee Classification Report.

What do you mean by 'grouping them'?

Do you want to count the last names (that you'd use group by for) or do you just want them sorted by the classification report (in which cause, it's a straightforward sort and needs an Order By)

--
Gail Shaw
SQL Server MVP

paultech
Yak Posting Veteran

79 Posts

Posted - 2011-10-16 : 06:04:18

hi ,

from your original post , i think you don't need to use groupby

you want to select lastname for all employers that have the same EmployerInformationReport so simply it will be

USE KudlerFineFoods
SELECT LastName FROM Employee
INNER JOIN dbo.JobTitle ON
JobTitle.JobID = Employee.JobID
where LastName in (select LastName from (select gg.LastName ,count(gg.LastName)as thecount from JobTitle as gg inner join JobTitle as kk on gg.EmployerInformationReport= kk.EmployerInformationReport
group by gg.LastName ) as dd where dd.thecount >1 )

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-10-16 : 07:32:04

quote:
Originally posted by paultech

hi ,

from your original post , i think you don't need to use groupby

you want to select lastname for all employers that have the same EmployerInformationReport so simply it will be

USE KudlerFineFoods
SELECT LastName FROM Employee
INNER JOIN dbo.JobTitle ON
JobTitle.JobID = Employee.JobID
where LastName in (select LastName from (select gg.LastName ,count(gg.LastName)as thecount from JobTitle as gg inner join JobTitle as kk on gg.EmployerInformationReport= kk.EmployerInformationReport
group by gg.LastName ) as dd where dd.thecount >1 )

good luck

paul Tech

you're still using group by in your query

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources