SQL count by column values

I'm trying to count multiple values in multiple coluns in a table and I did that:

TABLE

SQL:

select 'risk' as nm_test, risk as tst_result, count(*) as qty 
  from table
  group by risk 

union 

select 'record' as nm_test, record as tst_result, count(*) as qty
  from table
  group by record

union

select 'api' as nm_test, api as tst_result, count(*) as qty
  from table
  group by api

The result is the following table:

But, instead, I would like to have the result table pivoted like this:

I tried to do that, but I was unable to figure out the trick! Any help would be valuable.

Cheers.

If you have a fixed columns to pivot, and values are 'OK' and 'NO' only then :

select 'risk' as nm_test, 
        count(case when risk = 'OK' then 1 end) as 'OK',
        count(case when risk = 'NO' then 1 end) as 'NO'
from mytable
union all
select 'record', 
        count(case when record = 'OK' then 1 end),
        count(case when record = 'NO' then 1 end)
from mytable
union all
select 'api', 
        count(case when api = 'OK' then 1 end),
        count(case when api = 'NO' then 1 end)
from mytable

Demo here

Try this:

SELECT
  nm_test,
  SUM(CASE WHEN tst_result = 'OK' THEN qty ELSE 0 END) AS OK,
  SUM(CASE WHEN tst_result = 'NO' THEN qty ELSE 0 END) AS NO
FROM (
  SELECT 'risk' AS nm_test, risk AS tst_result, COUNT(*) AS qty
  FROM mytable
  GROUP BY risk

  UNION ALL
  SELECT 'record' AS nm_test, record AS tst_result, COUNT(*) AS qty
  FROM mytable
  GROUP BY record

  UNION ALL
  SELECT 'api' AS nm_test, api AS tst_result, COUNT(*) AS qty
  FROM mytable
  GROUP BY api
) subquery
GROUP BY nm_test;

Forget using UNION.

Pivoting from columns to rows in PostgreSQL can be done by casting into and out of jsonb. This helps if you will be adding more tests in the future.

with pivot as (
  select j.*
    from mytable
   cross join lateral jsonb_each_text(to_jsonb(mytable) - 'id') 
                        as j(nm_test, tst_result)
)
select nm_test, 
       count(*) filter (where tst_result = 'OK') as ok,
       count(*) filter (where tst_result = 'NO') as no
  from pivot
 group by nm_test;

Using - id gets rid of that column from the results.

Using filter on the aggregates pivots back to columns. This is useful so long as the possible results are finite and known.

Working example.