Count the unique and duplicate values in a column using Postgresql

Question

Goal: Find the count of uniques and duplicates in the worker_ref_id column.

I find the solution here in MySQL but IF does not exist in PostgreSQL. So, how would I do that in PostgreSQL?

I have the following table:

|worker_ref_id|bonus_amount|
|            1|        5000|
|            2|        3000|
|            3|        4000|
|            1|        4500|
|            2|        3500|

I would like the following output:

|Unique|Duplicates|
|1     |2         |

I get the right answer but it appears as two rows rather than two columns and one row:

SELECT COUNT(*) AS "Duplicate" FROM (SELECT worker_ref_id,
                  COUNT(worker_ref_id) AS "Count"
                  FROM bonus
                  GROUP BY worker_ref_id
                  HAVING COUNT(worker_ref_id) > 1) AS mySub
UNION

SELECT COUNT(*) AS "Unique" FROM (SELECT worker_ref_id,
                      COUNT(worker_ref_id) AS "Count"
                      FROM bonus
                      GROUP BY worker_ref_id
                      HAVING COUNT(worker_ref_id) = 1) AS mySub2

The `IF` function in MySQL can be replaced in PostgreSQL by `CASE WHEN {condition} THEN {value-if-true} ELSE {value-if-false} END` — foglerit, Aug 28 '19 at 01:34
Yes, I am aware of that. However, CASE WHEN THEN creates a new column, how I do filter it to get what I desire? Would I do a GROUP BY on CASE WHEN THEN column? If you know, could you write it out? — Rohit, Aug 28 '19 at 01:38

score 1 · Accepted Answer · answered Aug 28 '19 at 01:40

We can do this in two steps, using a CTE:

WITH cte AS (
    SELECT worker_ref_id, COUNT(*) AS cnt
    FROM bonus
    GROUP BY worker_ref_id
)

SELECT
    COUNT(*) FILTER (WHERE cnt = 1) AS "Unique",
    COUNT(*) FILTER (WHERE cnt > 1) AS Duplicates
FROM cte;

Count the unique and duplicate values in a column using Postgresql

1 Answers1