SQL Server and nested SELECT: ...Incorrect syntax near ')'?

Question

I get this error when executing my code. What does this mean? I'm using SQL Server Management Server Express.

Msg 102, Level 15, State 1, Line 7

Incorrect syntax near ')'.

declare @start datetime
declare @end datetime
set @start = '2012/01/02'
set @end = '2012/01/06'


SELECT  
 SUM(VCount) as [Total],
 vdate,
 (select COUNT(VIP) From (SELECT DISTINCT(VIP) FROM dbo.Visiter)) as [IP3]
FROM dbo.Visiter 
where VDate between @start and @end
GROUP BY VDate

Cheran Shunmugavel · Accepted Answer · 2012-01-06 08:56:22Z

7

In SQL Server, when you use a derived table (a subquery) in the FROM clause, you must give that derived table an alias:

SELECT  
 SUM(VCount) as [Total],
 vdate,
 -- add an alias in the next line
 (select COUNT(VIP) From (SELECT DISTINCT(VIP) FROM dbo.Visiter) AS a) as [IP3]
FROM dbo.Visiter 
where VDate between @start and @end
GROUP BY VDate

reference: FROM clause

answered Jan 6, 2012 at 8:56

Cheran Shunmugavel

8,4691 gold badge35 silver badges41 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

shybovycha · Accepted Answer · 2012-01-06 08:59:14Z

3

I think this

(select COUNT(VIP) From (SELECT DISTINCT(VIP) FROM dbo.Visiter)) as [IP3]

is better to transform to

(select COUNT(DISTINCT VIP) FROM dbo.Visiter) as [IP3]

AFAIK, MySQL at least says Every derived table must have its own alias so, your sub-query shoulda look like

(select COUNT(v) From (SELECT DISTINCT(VIP) AS v FROM dbo.Visiter) AS tmp1) as [IP3]

but the shorter - the better =)

edited Jan 6, 2012 at 8:59

answered Jan 6, 2012 at 8:51

shybovycha

12.4k6 gold badges54 silver badges86 bronze badges

9 Comments

user1133937 Over a year ago

id doing and get this : Msg 156, Level 15, State 1, Line 11 Incorrect syntax near the keyword 'SELECT'. Msg 102, Level 15, State 1, Line 11 Incorrect syntax near ')'.

user1133937 Over a year ago

i want get number of unique visitors but this get me sum all unique visitors . please help me (sorry for my weak english ;D)

shybovycha Over a year ago

@user1133937 which language do you prefer? =) and i did not understand your last question, i am sorry for that... i am a bit blunt last days =)

shybovycha Over a year ago

@user1133937 if you do want we can talk over GTalk or email for more detailed help =)

user1133937 Over a year ago

i have this table : i.sstatic.net/gmGdx.gif and get this : i.sstatic.net/YKeRR.gif but i want this i.sstatic.net/Ym42g.gif

|

Collectives™ on Stack Overflow

SQL Server and nested SELECT: ...Incorrect syntax near ')'?

2 Answers 2

Comments

9 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

9 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related