Question 199762 in PostgreSQL Question posted in
The official documentation can be found here.

Postgresql – SQL count by column values

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

TABLE

id risk record api
1 OK OK NO
2 OK OK OK
3 OK OK OK
4 OK NO OK
5 NO OK NO

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:

nm_test tst_result qty
risk OK 4
risk NO 1
record OK 4
record NO 1
api OK 3
api NO 2

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

nm_test OK NO
risk 4 1
record 4 1
api 3 2

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

Cheers.

Tags:

3

Answers


  1. 0

    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

    Login or Signup to reply.
  2. 0

    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;
    Login or Signup to reply.
  3. 0

    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.

    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search