Error with SQL Server query

Question

I want to select the columns TeamProjectProjectNodeName & TestPlanName from the inner subquery. The inner query runs perfectly fine in fetching results from the view. But I am getting the below error, when I run the entire query as below -

Incorrect syntax near ')'.

Please help me with the same.

Here is my query -

select 
    x.TeamProjectProjectNodeName, x.TestPlanName
from 
    (select 
         TeamProjectProjectNodeName, TestPlanName,ResultOutcome, count(ResultOutcome) 
     from [Tfs_Warehouse].[dbo].[TestResultView] 
     where TestPlanName <> 'NULL'
     GROUP BY TeamProjectProjectNodeName, TestPlanName, ResultOutcome
     order by TeamProjectProjectNodeName asc, TestPlanName asc) x

Thanks.

A few questions, if you run the subquery on its own does it run? In the existing query, remove the order by from the subquery and place an alias on the count(). Do you mean to use <> null or do you want is not null? — Taryn
– Taryn, Commented Jun 4, 2013 at 15:06

Paul Grimshaw · Accepted Answer · 2013-06-04 15:17:17Z

4

Bring order by out of the sub query, and name the count column like this:

select 
   x.TeamProjectProjectNodeName,
   x.TestPlanName,
   x.Total
from (select 
         TeamProjectProjectNodeName,
         TestPlanName,
         ResultOutcome, 
         count(*) as Total 
      from [Tfs_Warehouse].[dbo].[TestResultView] 
      where TestPlanName IS NOT NULL
      GROUP BY TeamProjectProjectNodeName, TestPlanName, ResultOutcome
     ) x
order by TeamProjectProjectNodeName asc, TestPlanName asc

I have also changed your WHERE syntax to IS NOT NULL as @bluefeet suggested, as I imagine this is what you actually require.

edited Jun 4, 2013 at 15:17

answered Jun 4, 2013 at 15:06

Paul Grimshaw

21.2k6 gold badges43 silver badges60 bronze badges

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

1 Comment

Taryn Over a year ago

I would suggest possibly changing or making a note to change the TestPlanName <> 'NULL' to TestPlanName is not null. If they are really testing for null. The first will not work unless they are actually storing null as a string.

Dominic Goulet · Accepted Answer · 2013-06-04 15:06:53Z

1

Here you go :

select x.TeamProjectProjectNodeName,x.TestPlanName
from (select top 100 percent TeamProjectProjectNodeName,TestPlanName,ResultOutcome, count(ResultOutcome) SomeAlias from [Tfs_Warehouse].[dbo].[TestResultView] 
where TestPlanName <> 'NULL'
GROUP BY TeamProjectProjectNodeName, TestPlanName, ResultOutcome
order by TeamProjectProjectNodeName asc, TestPlanName asc) x

You cannot have a order by in your inner select unless you specify top and the count() should have an alias.

answered Jun 4, 2013 at 15:06

Dominic Goulet

8,1437 gold badges33 silver badges57 bronze badges

3 Comments

Taryn Over a year ago

You might want to remove the order by from the subquery. :)

Dominic Goulet Over a year ago

You can use it with a top 100 percent. But is that useful? ;-)

Dominic Goulet Over a year ago

Yeah I have to agree it is horrible practice.

Fabio · Accepted Answer · 2013-06-04 15:07:40Z

0

I would suggest to give your count() an alias and group by aggregate function to avoid the error

select x.TeamProjectProjectNodeName,x.TestPlanName
from (select TeamProjectProjectNodeName,TestPlanName,ResultOutcome, count(ResultOutcome) as total from [Tfs_Warehouse].[dbo].[TestResultView] 
where TestPlanName <> 'NULL'
GROUP BY TeamProjectProjectNodeName, TestPlanName, ResultOutcome) x

answered Jun 4, 2013 at 15:07

Fabio

23.5k12 gold badges57 silver badges66 bronze badges

Collectives™ on Stack Overflow

Error with SQL Server query

3 Answers 3

1 Comment

3 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

1 Comment

3 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related