CODEWITHSUNDEEP
sqlsql-server-2008
JPro
JPro

Reputation: 6556

error executing sql server query

I am getting an error using this query

select dbresultsid, TestCase, BuildID, Analyzed,
     Verdict, 
     (select count(Verdict) from results where BuildID = 'Beta1' 
             and Verdict = 'PASS') AS PASS, 
     (select count(Verdict) from results where BuildID = 'Beta1' 
             and Verdict = 'FAIL') AS FAIL, 
     (select count(Verdict) from results where BuildID = 'Beta1' 
             and Verdict = 'INCONC') AS INCONC, 
     (select count(Verdict) from results where BuildID = 'Beta1' 
             and Verdict = 'TIMEOUT') AS TIMEOUT 
from results 
where BuildID = 'Beta1'       
group by TestCase,dbresultsid 
order by Analyzed

It says

Column 'results.BuildID' is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause.'

The same query runs fine in MYSQL. Can anyone help?

Thanks,

Upvotes: 1

Views: 321

Answers (5)

Mladen Prajdic
Mladen Prajdic

Reputation: 15685

You either have to aggregate your BuildID and Analyzed columns or add them to the group by. Also note that the way you're doing it now is horribly inefficient.

Take a look here how you can improve your performance by doing only one table scan instead of several hundred you're going to have

Upvotes: 0

MicSim
MicSim

Reputation: 26826

If you use Group By in your query, you must add all columns that aren't aggregate functions to your Group By clause. So add BuildID to the Group By clause and all other columns without aggregates (like Verdict, etc.), and it will work.

Also see the GROUP BY documentation for further details.

Upvotes: 1

yu_sha
yu_sha

Reputation: 4400

Group by means - group the results by listed fields, and execute some aggregate function (sum,avg...) on the rest of the fields. Everything that does not have count() or avg() or sum() must be in group by.

However, it looks like you mixed two different queries together. If you need a number of records with each verdict for a given BuildId, the query is simply:

select BuildID, Verdict, count(*)
group by BuildID, Verdict

If that's not what you want, you should describe what you are trying to achieve

Upvotes: 0

cjk
cjk

Reputation: 46485

Change you code to be this:

select dbresultsid, TestCase, BuildID, Analyzed,
     Verdict, 
     (select count(Verdict) from results where BuildID = 'Beta1' 
             and Verdict = 'PASS') AS PASS, 
     (select count(Verdict) from results where BuildID = 'Beta1' 
             and Verdict = 'FAIL') AS FAIL, 
     (select count(Verdict) from results where BuildID = 'Beta1' 
             and Verdict = 'INCONC') AS INCONC, 
     (select count(Verdict) from results where BuildID = 'Beta1' 
             and Verdict = 'TIMEOUT') AS TIMEOUT 
from results 
where BuildID = 'Beta1'       
group by TestCase,dbresultsid, BuildID, Analyzed
order by Analyzed

Whilst you are filtering BuildID to only be one value, SQL Server requires that it be specified in the group by list.

Upvotes: 1

Ashish Jain
Ashish Jain

Reputation: 4807

Group By Clause in SQL Server allows columns which are in Group By Clause and in case we want some other columns then we need to access them via Aggregate functions like MAX, MIN etc.

Upvotes: 0

Related Questions