Problem using good query as subquery 2

[4946]

I cannot identify what the problem is that SQL reports (Server: Msg 156, Level 15, State 1, Line 32
Incorrect syntax near the keyword 'Group'.) when Trying to use this query and subquery. The subquery runs fine as a stand-alone query just as it is. This is a SQL 2000 database.

Thanks in advance for your help, I am in the process of learning to port my ACCESS queries to SQL syntax.
*******************

Select dbo.interactiontrack.workgroupid, count(dbo.interactiontrack.userid) as STCNT 

FROM
(SELECT  distinct dbo.interactiontrack.workgroupid, dbo.interactiontrack.userid 
	FROM  dbo.interactiontrack
	WHERE    dbo.interactiontrack.enddate > datediff(ss, '01/01/1970', getutcdate()) - (datediff(ss, '01/01/1970', getutcdate()) % 3600) - 5400 
	
	and 

	(dbo.interactiontrack.enddate 
	BETWEEN 
	(CASE 	WHEN datediff(ss, '01/01/1970', getutcdate()) % 3600 < 1801 
			THEN datediff(ss, '01/01/1970', getutcdate()) - (datediff(ss, '01/01/1970', getutcdate()) % 3600) - 1800 
		WHEN datediff(ss, '01/01/1970', getutcdate()) % 3600 > 1800 
			THEN datediff(ss, '01/01/1970', getutcdate()) - (datediff(ss, '01/01/1970', getutcdate()) % 3600) END) 
	
	AND 
        
	CASE 	WHEN datediff(ss, '01/01/1970', getutcdate()) % 3600 < 1801 
			THEN datediff(ss, '01/01/1970', getutcdate()) - (datediff(ss, '01/01/1970', getutcdate()) 
                      % 3600) 
		WHEN datediff(ss, '01/01/1970', getutcdate()) % 3600 > 1800 
			THEN datediff(ss, '01/01/1970', getutcdate()) - (datediff(ss, '01/01/1970', getutcdate()) % 3600) + 1800 END) 

AND 
dbo.interactiontrack.workgroupid > 0 
AND
dbo.interactiontrack.userid > 0

group by dbo.interactiontrack.workgroupid, dbo.interactiontrack.userid)

Group By dbo.interactiontrack.workgroupid

 

[vongrunt]

Simple: name it. After ), before GROUP.

------
"There's a man... He's bald and wears a short-sleeved shirt, and somehow he's very important to me. I think his name is Homer."
(Jack O'Neill, Stargate)

 

[4946]

vongrunt,
Hey, thanks for the quick reply!! I am a bit foggy-headed today, could you give me some more detail??
Thanks,
WinN

 

[SQLBill]

What Vongrunt is trying to tell you is that when you use a SUBQUERY in place of a table in the FROM, the subquery must have an alias....that alias becomes the table name.

EX.
SELECT....
FROM (SELECT ....) A

A becomes the tablename.

-SQLBill

Posting advice: FAQ481-4875

 

[4946]

SQLBill,

Thanks for the details. Works perfectly now..

vongrunt,

Thanks again for your quick assistance, sorry I didn't catch it right off.

WinN