group by does not work - 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 by does not work

Author

Topic

h0ppel
Starting Member

1 Post

Posted - 2012-04-22 : 16:58:30

Hi,

does anybody know, why my comment does not work?


SELECT Employee.Lastname, Employee.Firstnme, EMP_ACT.Projno 
FROM Employee Join EMP_ACT On Employee.Empno = EMP_Act.Empno 
GROUP BY EMP_ACT.Projno 
HAVING COUNT(*) >= 3

this is the error:

SQL0119N An expression starting with "FIRSTNME" specified in a SELECT clause,
HAVING clause, or ORDER BY clause is not specified in the GROUP BY clause or
it is in a SELECT clause, HAVING clause, or ORDER BY clause with a column
function and no GROUP BY clause is specified. SQLSTATE=42803

thanks

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2012-04-22 : 18:09:49

Your spelled Firstname wrong. It's missing an "a".

You're query needs to add aggregate functions though. Which LastName/FirstName combo do you want returned per Projno?

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to my blog

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-04-22 : 21:32:19

seems like what you need is


SELECT e.Lastname, e.Firstname, ea.Projno 
FROM Employee e
Join (SELECT Empno,Projno FROM EMP_ACT 
GROUP BY Empno,Projno 
HAVING COUNT(*) >= 3
)ea
On e.Empno = ea.Empno

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

vinu.vijayan
Posting Yak Master

227 Posts

Posted - 2012-04-23 : 08:26:10

I agree with Visakh.
If you could post some DDL and sample data, we could provide better solutions rather than shooting in the dark like this.

N 28° 33' 11.93148"
E 77° 14' 33.66384"

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2012-04-24 : 06:16:51

Also note that this is MS SQL server forum for DB2 questions post at relevent forum

Madhivanan

Failing to plan is Planning to fail

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources