error executing sql server query

Question

Hi Guys,

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,

Answer 1

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.

Answer 2

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.

Answer 3

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

Answer 4

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.

Answer 5

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