difference in these queries

Question

Hi, I am executing a query different way in MSSQL, but the second query is not giving result as the first one.

Query 1:
    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

Query 2:

select dbresultsid, TestCase, BuildID, Analyzed,
         Verdict, 
(case when Verdict='PASS' then count(Verdict) else 0 end) as PASS,
(case when Verdict='FAIL' then count(Verdict) else 0 end) as FAIL,
(case when Verdict='INCONC' then count(Verdict) else 0 end) as INCONC,
(case when Verdict='TIMEOUT' then count(Verdict) else 0 end) as TIMEOUT
from results
where 
BuildID = 'Beta1'
group by TestCase,dbresultsid 
order by Analyzed


Results :
for Query 1:
if the total number of PASS = 20,
 then PASS column will display 20 everywhere.

Results :
for Query 2:
here whereever there is PASS, it displays 1 and the total 20 rows where pass is displayed there is 1,

I want the results of query 2 to be same as query 1

any ideas please?

thanks,

Answer 1

The first query does a SELECT with a WHERE to return only the rows that have a matching verdict and then counts those rows. The second query counts all rows every time.

You might try:

select 
  dbresultsid, TestCase, BuildID, Analyzed, Verdict, 
  Sum(case when Verdict='PASS' then 1 else 0 end) as PASS,
  Sum(case when Verdict='FAIL' then 1 else 0 end) as FAIL,
  Sum(case when Verdict='INCONC' then 1 else 0 end) as INCONC,
  Sum(case when Verdict='TIMEOUT' then 1 else 0 end) as TIMEOUT
from 
  results
where 
  BuildID = 'Beta1'
group by 
  TestCase, dbresultsid 
order by 
  Analyzed

Test data:

  CREATE TABLE #Test (BuildID Integer, Verdict char(7))
  INSERT INTO #TEST (Buildid, Verdict)
    VALUES (1, 'PASS')
  INSERT INTO #TEST (Buildid, Verdict)
    VALUES (1, 'PASS')
  INSERT INTO #TEST (BuildID, Verdict)
    VALUES (2, 'FAIL')
  INSERT INTO #TEST (BuildID, Verdict)
    VALUES (3, 'INCONC')
  INSERT INTO #TEST (BuildID, Verdict)
    VALUES(4, 'TIMEOUT')

Query:

select buildid,
  sum(case verdict when 'PASS' then 1 else 0 end) as Pass,
  sum(case verdict when 'FAIL' then 1 else 0 end) as Fail,
  sum(case verdict when 'INCONC' then 1 else 0 end) as Inconc,
  sum(case verdict when 'TIMEOUT' then 1 else 0 end) as TimeOut
FROM #temp
group by buildid

Output:

Item    buildid PASS Fail Inconc TimeOut 
1         1      2       0        0        0
2         2      0       1        0        0
3         3      0       0        1        0
4         4      0       0        0        1

Answer 2

Why don't you just use the first one?, In my opinion is a nice way to get your results if the keys for reference and group by are well defined. I only will change the "where" in the subselects for not repeating the key each time, making a direct reference to the main table instead.

(select count .. from results where BuildID = r1.BuildID and Verdict  ..)
from results r1

Answer 3

First of all, since you mention in comment that dbresultsid is a key column, Including it in the group by clause is ineffective, you will get one output row for every row in original table (that matches your where clause).

Secondly, because the subqueries in first query are un-correlated, their output is not dependant on the row from the outer query. Therefore they will only be executed once, and the same generated value will be repeated in every outout row.

So, If the output of the first query is really what you want (where there is one row per original 'Beta1' row in results table and every row in output has the same values in the last 4 columns) then what you have is pretty close to best you can do. Just take out the group by clause - you don't need it.

Select 
  dbresultsid, TestCase, BuildID, Analyzed, Verdict,  
  z.PASS, z.FAIL, z.INCONC, z.TIMEOUT
From results r Cross Join 
  (Select 
     Sum(case when Verdict='PASS' then 1 else 0 end) PASS,
     Sum(case when Verdict='FAIL' then 1 else 0 end) FAIL,
     Sum(case when Verdict='INCONC' then 1 else 0 end) INCONC,
     Sum(case when Verdict='TIMEOUT' then 1 else 0 end) TIMEOUT
   From results Where BuildID = 'Beta1') Z
Where BuildID = 'Beta1'
Order By Analyzed