0

I have a table made of 2 columns:

Tested_Object | Result

     A        |  OK
     A        |  NOT OK
     A        |  NOT OK
     B        |  OK

And I need to have an output like the following:

Tested_Object | SUM    | OK    | NOT_OK

     A        |  3     |1      | 2 
     B        |  1     |1      | 0 (or empty)

I tried using:

SELECT
t1.Tested_Object,
Count(t1.Result) AS SUM,
Count(t2.Result) AS OK,
Count(t3.Result) AS NOT_OK
FROM
(t1 LEFT JOIN (t1 AS t2) ON t1.Tested_Object=t2.Tested_Object) 
LEFT JOIN (t1 AS t3) WHERE t1.Tested_Object=t3.Tested_Object 

GROUP BY t1.Tested_Object

now if I put:

WHERE (t2.Result="OK" AND t3.Result="NOT_OK")

or

WHERE (t2.Result="OK" OR t3.Result="NOT_OK")

or

t1 LEFT JOIN (t1 AS t2 WHERE t2.Result="OK") ON t1.Tested_Object=t2.Tested_Object

I get the same count number for each column or error.

I managed to get different columns numbers only saving all the OKs in a table and the NOT OKs in another table manually.. But I need a query that does the calculation completely automatically from input to output table.

If not clear, I'm a newbie with SQL :) Thanks in advance

Lucia Belardinelli
  • 727
  • 1
  • 11
  • 20

2 Answers2

1

MySQL Solution:

You can make use of CASE ... WHEN ... THEN to differentiate OK values from NOT OK values. 

SELECT Tested_Object
     , COUNT( Tested_Object ) AS `SUM`
     , SUM( CASE WHEN Result = 'OK' THEN 1 ELSE 0 END ) AS `OK`
     , SUM( CASE WHEN Result = 'NOT OK' THEN 1 ELSE 0 END ) AS `NOT_OK`
  FROM t1
 GROUP BY Tested_Object

MS Access Solution:

You can make use of IIF Function

SELECT Tested_Object
     , COUNT( Tested_Object ) AS SUM
     , SUM( IIF( Result = 'OK', 1, 0 ) ) AS OK
     , SUM( IIF( Result = 'NOT OK', 1, 0 ) ) AS NOT_OK
  FROM t1
 GROUP BY Tested_Object
Ravinder Reddy
  • 23,692
  • 6
  • 52
  • 82
0
SELECT Tested_Object
 , count(*) SUM
 ,  Count(CASE
           WHEN Result = 'OK' THEN 1
         END) OK
 ,Count(CASE
           WHEN Result = 'NOT_OK' THEN 1
         END) NOTOK
FROM my_table 
GROUP 
BY Tested_Object;

Find solution in fiddle http://sqlfiddle.com/#!6/22986/1

Sabyasachi Mishra
  • 1,677
  • 2
  • 31
  • 49