mysql - Create a table pivot-like output with SQL/Access -

i have table made of 2 columns:

tested_object | result              |  ok             |  not ok             |  not ok      b        |  ok 

and need have output following:

tested_object | sum    | ok    | not_ok              |  3     |1      | 2       b        |  1     |1      | 0 (or empty) 

i tried using:

select t1.tested_object, count(t1.result) sum, count(t2.result) ok, count(t3.result) not_ok (t1 left join (t1 t2) on t1.tested_object=t2.tested_object)  left join (t1 t3) t1.tested_object=t3.tested_object   group t1.tested_object 

now if put:

where (t2.result="ok" , t3.result="not_ok") 

or

where (t2.result="ok" or t3.result="not_ok") 

or

t1 left join (t1 t2 t2.result="ok") on t1.tested_object=t2.tested_object 

i same count number each column or error.

i managed different columns numbers saving oks in table , not oks in table manually.. need query calculation automatically input output table.

if not clear, i'm newbie sql :) in advance

mysql solution:

you can make use of case ... when ... then differentiate ok values not ok values.

select tested_object      , count( tested_object ) `sum`      , sum( case when result = 'ok' 1 else 0 end ) `ok`      , sum( case when result = 'not ok' 1 else 0 end ) `not_ok`   t1  group tested_object 

ms access solution:

you can make use of iif function

select tested_object      , count( tested_object ) sum      , sum( iif( result = 'ok', 1, 0 ) ) ok      , sum( iif( result = 'not ok', 1, 0 ) ) not_ok   t1  group tested_object