May 2, 2008 at 5:13 am
I have one senario.
Query:
Update Emp
set CounterPart = count(*)
from Emp1 A,Dept B
where A.Eno = '101'
GROUP BY A.Eno,Datepart(Month,JoinDate),Datepart(Year,JoinDate)
When i try to execute the above query ,I got the following error message.
Incorrect syntax near the keyword 'Group'
Quick Inputs are highly appreciable !
karthik
May 2, 2008 at 5:21 am
Sorry, i posted wrong query !
Query:
Update Emp
set CounterPart = count(*)
from Emp1 A,Dept B
where A.DeptNo = b.DeptNo
AND A.Eno = '101'
GROUP BY A.Eno,Datepart(Month,JoinDate),Datepart(Year,JoinDate)
karthik
May 2, 2008 at 5:25 am
You can not do an aggregate in the set list of an UPDATE statement ,
set CounterPart = count(*)
May 2, 2008 at 5:39 am
Ok.
Basically, what i want to display is
I want to display the count which is having '0' records in Dept table.
so i have written the query like
select A,Eno,Datepart(Month,JoinDate),Datepart(Year,JoinDate),count(*)
from Emp1 A,Dept B
where A.DeptNo = b.DeptNo
AND A.Eno = '101'
GROUP BY A.Eno,Datepart(Month,JoinDate),Datepart(Year,JoinDate)
But it is showing only the matching resord.
Say for example i have 135 records in Emp table, out of 135, only 125 records having data in Dept table, others don't have any data. It means '0'. I want to display that records only.
Above query is showing 125 records not that remaining 10 records.
I tried Left outer join to do it,but again i got below the error message.
" The table 'emp' is an inner member of an outer-join clause. This is not allowed if the table also participates in a regular join clause. "
karthik
May 2, 2008 at 5:47 am
Hi,
Try this...
Select A.Eno,Datepart(Month,JoinDate),Datepart(Year,JoinDate),count(*)
From Emp1 A
Left Outer Join Dept B on A.DeptNo = B.DeptNo
where A.Eno = '101'
GROUP BY A.Eno,Datepart(Month,JoinDate),Datepart(Year,JoinDate)
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply