Click here to Skip to main content
11,411,263 members (64,434 online)
Rate this: bad
good
Please Sign up or sign in to vote.
See more: SQL-Server-2008
select c.*,COUNT(p.empid) from empcdetails c inner join emppdetails p on p.empid=c.empid where empstatus ='project' group by p.empid order by MAX(c.empdoj)asc
Posted 28-Dec-12 7:11am
Edited 28-Dec-12 7:17am
v2

1 solution

Rate this: bad
good
Please Sign up or sign in to vote.

Solution 1

c.* is the problem here. As you are using Group By clause, you have to mention only specific column names in your select list that are in group by.

Try this instead.
select p.empid,COUNT(p.empid) 
from empcdetails c 
inner join emppdetails p 
on p.empid=c.empid 
where empstatus = 'project' 
group by p.empid 
order by MAX(c.empdoj)asc

Try this subquery version, if you want to have all columns


select c.* , A.myCount
from empcdetails  c
join 
(
	select p.empid,COUNT(p.empid) as myCount
	from empcdetails cc 
	inner join emppdetails p 
	on p.empid = cc.empid 
	group by p.empid 
) A
on A.empid = c.empid
where empstatus = 'project' 
order by MAX(c.empdoj)asc
 


Hope this helps you.
  Permalink  
v2
Comments
Zoltán Zörgő at 28-Dec-12 13:25pm
   
My 5! You are right, one has to list all non-aggregated fields in the group by clause.
Sheikh Muhammad Haris at 28-Dec-12 14:19pm
   
Thanks :)
Member 9576671 at 28-Dec-12 22:11pm
   
thanks but my requirement is displaying of all columns in emppdetails
Sheikh Muhammad Haris at 29-Dec-12 8:00am
   
Then you have to use it in a sub query, otherwise you would not be able to apply Group By on your query.
Sheikh Muhammad Haris at 29-Dec-12 8:21am
   
I have edited my answer above with the sub query version if you want to display all columns. Try that
Sheikh Muhammad Haris at 30-Dec-12 18:02pm
   
Does it helps you ?

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

  Print Answers RSS
0 Sergey Alexandrovich Kryukov 189
1 Maciej Los 98
2 OriginalGriff 88
3 Sascha Lefévre 85
4 BillWoodruff 80
0 Sergey Alexandrovich Kryukov 8,920
1 OriginalGriff 6,983
2 Maciej Los 3,430
3 Abhinav S 3,248
4 Peter Leow 3,059


Advertise | Privacy | Mobile
Web03 | 2.8.150414.5 | Last Updated 29 Dec 2012
Copyright © CodeProject, 1999-2015
All Rights Reserved. Terms of Service
Layout: fixed | fluid

CodeProject, 503-250 Ferrand Drive Toronto Ontario, M3C 3G8 Canada +1 416-849-8900 x 100