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
– foglerit, Commented Aug 28, 2019 at 1: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
– Rohit, Commented Aug 28, 2019 at 1:38

Tim Biegeleisen · Accepted Answer · 2019-08-28 01:40:34Z

2

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;

answered Aug 28, 2019 at 1:40

Tim Biegeleisen

526k32 gold badges323 silver badges399 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

Count the unique and duplicate values in a column using Postgresql

1 Answer 1

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related