How to select specific value from a table that has multiple values for a record in primary key table in PostgreSQL?
Single pass using DISTINCT ON

We can use DISTINCT ON in subselect to get the users highest priority rights. Then we need only count the rights by those groups.

It's important to note, we're joining to rights here. We needn't do that. You could adopt the method of simplicity used in @JackDouglas's answer to compare on ints. It saves a lot of time by not joining but because we only do one table scan we're still twice as fast. In production, you may want to skip the join on rights.

SELECT
  organization,
  count(rightgrp) FILTER (WHERE rightgrp = 1) AS "Reviewer",
  count(rightgrp) FILTER (WHERE rightgrp = 2) AS "Data Entry User",
  count(rightgrp) FILTER (WHERE rightgrp = 3) AS "Read Only User"
FROM (
  SELECT DISTINCT ON (organization, uid)
    organization,
    uid,
    CASE
      WHEN rights.name IN ('Reviewer', 'High Reviewer') THEN 1
      WHEN rights.name IN ('Data Entry') THEN 2
      ELSE 3
    END AS rightgrp
  FROM rights
  JOIN user_rights_rel
    ON rid = rights.id
  JOIN users
    ON uid = users.id
  ORDER BY 1, 2, 3
) AS t
GROUP BY organization
ORDER BY organization;

 organization | Reviewer | Data Entry User | Read Only User 
--------------+----------+-----------------+----------------
 A            |        2 |               2 |              1
 B            |        1 |               1 |              3
(2 rows)


Using the tablefunc extension and crosstab

I wouldn't use this method, it's too sledge-hammery for what you need.

Here is a method that users crosstab for display, though it doesn't have to,

SELECT *
FROM crosstab($$
  SELECT organization, rightgrp, count(*)
  FROM (
    SELECT DISTINCT ON (organization, uid)
      organization,
      uid,
      CASE
        WHEN rights.name IN ('Reviewer', 'High Reviewer') THEN 1
        WHEN rights.name IN ('Data Entry') THEN 2
        ELSE 3
      END AS rightgrp
    FROM rights
    JOIN user_rights_rel
      ON rid = rights.id
    JOIN users
      ON uid = users.id
    ORDER BY 1, 2, 3
  ) AS t
  GROUP BY organization, rightgrp
  ORDER BY 1,2
$$, $$
  SELECT * FROM (VALUES (1),(2),(3)) AS t(cat)
$$
) AS ct(organization text, "Reviewer" int, "Data Entry User" int, "Read Only User" int);


First, we optimize the table read to a single pass which we select only the highest-privilege rights for the (organization,user).

SELECT DISTINCT ON (organization, uid)
    organization,
    uid,
    CASE
      WHEN rights.name IN ('Reviewer', 'High Reviewer') THEN 1
      WHEN rights.name IN ('Data Entry') THEN 2
      ELSE 3
    END AS rightgrp
  FROM rights
  JOIN user_rights_rel
    ON rid = rights.id
  JOIN users
    ON uid = users.id
  ORDER BY 1, 2, 3;

 organization | uid | rightgrp 
--------------+-----+----------
 A            |   1 |        1
 A            |   2 |        1
 A            |   3 |        2
 A            |   4 |        2
 A            |   5 |        3
 B            |   6 |        3
 B            |   7 |        3
 B            |   8 |        2
 B            |   9 |        1
 B            |  10 |        3
(10 rows)


Then we simply GROUP BY organization, rightgrp and select the count, like this

SELECT DISTINCT ON (organization, uid)
  organization,
  uid,
  CASE
    WHEN rights.name IN ('Reviewer', 'High Reviewer') THEN 1
    WHEN rights.name IN ('Data Entry') THEN 2 
    ELSE 3 
  END AS rightgrp
FROM rights
JOIN user_rights_rel
  ON rid = rights.id
JOIN users
  ON uid = users.id
ORDER BY 1, 2, 3 

 organization | rightgrp | count 
--------------+----------+-------
 A            |        1 |     2
 A            |        2 |     2
 A            |        3 |     1
 B            |        1 |     1
 B            |        2 |     1
 B            |        3 |     3
(6 rows)


From there we can feed this to pivot using PostgreSQL's tablefunc extension which provides crosstab. We can generate the pivot manually though using crafty SELECT/FILTER. Keep in mind right #1 is the highest priority

 organization | Reviewer | Data Entry User | Read Only User 
--------------+----------+-----------------+----------------
 A            |        2 |               2 |              1
 B            |        1 |               1 |              3


If you want to generate it without crosstab..