Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium


syntax error for subselect

Posted on 2005-03-25
Medium Priority
Last Modified: 2008-01-09
I am running  sql server desktop edition (MSDE 2000).

I need a count of a union:

select count (*) from ( select id from member union select id from dropoffpoint  )

Running the above in the Query analyzer SQL Server yields this error:
Server: Msg 170, Level 15, State 1, Line 1
Line 1: Incorrect syntax near ')'.

Even a simplified version:
select * from (select id from member)
yields the same error.

Can anyone pls try something similar on MSDE? On the fullblown SQL Server?
Any help appreciated....I do not wish to resort to a temp table because it should be unnecessary here.
Question by:stuart_wachsberg
  • 3
  • 2
LVL 66

Expert Comment

by:Jim Horn
ID: 13633694
Hi stuart_wachsberg,

In Access VBA, you can't count off of the asterisk *, you have to Count(one of your fields).  Perhaps SQL Server SQL is the same.

Hope this helps.
LVL 75

Accepted Solution

Anthony Perkins earned 1000 total points
ID: 13633720
Try it this way:
select count (*) from ( select id from member union select id from dropoffpoint) a
LVL 75

Expert Comment

by:Anthony Perkins
ID: 13633733
>>select * from (select id from member)<<
Same story here, you need to alias your FROM as in :
select * from (select id from member) a

What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.


Author Comment

ID: 13633778
how did u know, acperkins? Is this unique to sql server? I have never seeen this before.

Thanks for the quick and helpful response.
LVL 75

Expert Comment

by:Anthony Perkins
ID: 13633935
>>how did u know, acperkins?<<
I think I learned that here <g>

Author Comment

ID: 13634391
its rather odd, but u rescued me. thanks again and Happy Holiday.

Featured Post


Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

In part one, we reviewed the prerequisites required for installing SQL Server vNext. In this part we will explore how to install Microsoft's SQL Server on Ubuntu 16.04.
This month, Experts Exchange sat down with resident SQL expert, Jim Horn, for an in-depth look into the makings of a successful career in SQL.
Using examples as well as descriptions, and references to Books Online, show the documentation available for date manipulation functions and by using a select few of these functions, show how date based data can be manipulated with these functions.
Viewers will learn how to use the SELECT statement in SQL to return specific rows and columns, with various degrees of sorting and limits in place.
Suggested Courses

580 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question