CODEWITHSUNDEEP
sql-server
0nir
0nir

Reputation: 1355

Error with SQL Server query

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.

Upvotes: 0

Views: 102

Answers (3)

Paul Grimshaw
Paul Grimshaw

Reputation: 21044

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.

Upvotes: 4

Fabio
Fabio

Reputation: 23510

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

Upvotes: 0

Dominic Goulet
Dominic Goulet

Reputation: 8113

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.

Upvotes: 1

Related Questions