Click here to Skip to main content
13,595,908 members
Rate this:
 
Please Sign up or sign in to vote.
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 6:11am
Updated 28-Dec-12 6: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ő 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 28-Dec-12 14:19pm
   
Thanks :)
Sheikh Muhammad Haris 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 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 30-Dec-12 18:02pm
   
Does it helps you ?
28-Dec-12 22:11pm
   
thanks but my requirement is displaying of all columns in emppdetails

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

  Print Answers RSS
Top Experts
Last 24hrsThis month


Advertise | Privacy | Cookies | Terms of Service
Web02 | 2.8.180621.3 | Last Updated 29 Dec 2012
Copyright © CodeProject, 1999-2018
All Rights Reserved.
Layout: fixed | fluid

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