How to count DISTINCT occurrences of a single value across two different columns in PostgreSQL?-CodePudding

I have this table called "match" with these columns:

id | matcher | partner | meetup | date | block | delay | started | rejected

My goal is extract count of distinct users in a month format like this:

  month  | monthly_users
--------- ---------------
 2021-12 |             4
 2022-02 |             6

The problem is that the users are in two columns: matcher and partner, meaning that I have to count only values that are distinct accross those two columns because a single user can be both in matcher and partner fields.

For example

id | matcher | partner | meetup | date | block | delay | started | rejected
1  | user 1 | user 2| ...
2  | user 3 | user 1| ...

I want to count user 1 only ONE time in THAT MONTH.

I've done it for a single column like this:

select 
TO_CHAR(date_trunc('month', "date"), 'YYYY-MM') AS month, 
COUNT(DISTINCT "matcher") AS unique_matchers
from match
GROUP BY month;

But failed for both columns, here is my attempt:

select
TO_CHAR(date_trunc('month', "date"), 'YYYY-MM') AS month,
count(user) AS monthly_users
from ((select DISTINCT matcher as user, date from match) union all
      (select DISTINCT partner as user, date from match)
     ) as derived
GROUP BY user, month;

CodePudding user response：

You can join 2 selects in a sub-query using UNION ALL

SELECT
p.month,
COUNT( p.player) monthly_users
FROM
(
SELECT 
TO_CHAR(date_trunc('month', "date"), 'YYYY-MM') AS month, 
matcher AS player
FROM match
UNION 
SELECT 
TO_CHAR(date_trunc('month', "date"), 'YYYY-MM') AS month, 
partner AS player
FROM
) p
GROUP BY p.month
ORDER BY p.month;

CodePudding user response：

Your try was ok. Instead of union all just use union and in this way you will have distinct values. No need for use of DISTINCT

select TO_CHAR(date_trunc('month', "date_c"), 'YYYY-MM') AS month
       , count(user) AS monthly_users
from ((select matcher as usera, date_c from match) union
      (select partner as usera, date_c from match)
     ) as derived
GROUP BY  month;

also I have changed word user into usera because it is a keyword.

Here is a demo

Here is a new code after I have read the correct comments...

select TO_CHAR(date_trunc('month', "month"), 'YYYY-MM')
       , count(usera) AS monthly_users
from (select matcher as usera, date_trunc('month', "date_c") AS month 
      from match 
      union
      select partner as usera, date_trunc('month', "date_c") AS month
      from match
      group by usera, month) as derived
GROUP BY  month;

Here is a new DEMO

I see also that this is the same as accepted answer....