Removing duplicate entries whilst joining

Question

user	learning_group	program	learning_group_user	learning_group_program
id	id	id	id	id
name	name	name	learning_group_id	learning_group_id
			user_id	program_id

I am working with Postgres. My app will track users and what programs they are assigned to. User are associated with programs by adding them to learning groups. If a user is assigned to a learning group and a program is assigned to that learning group, then he is eligible to complete that program.

Users can be assigned to many learning groups.

Programs can also be assigned to many learning groups

I want to get the total number of users that are associated to a particular program, but, I don't want to count users more than once if they are assigned to multiple learning groups and the program is also associated with those same learning groups.

For example: if user "John Smith" is assigned to 3 learning groups, and, "Science Program" is assigned to all 3 of those learning groups, when I get the total number of users that are eligible to complete the "Science Program", John should only be counted once, even though naturally he would be counted 3 times because he is associated with the program 3 times via the 3 different learning groups.

My plan is to achieve this as a sub query, so when I query a program by id, I can include the number of users that are enrolled in that program:

SELECT 
  p.id, 
  p.data, 
  (
    SELECT count(u.id)
    FROM learning_group_user lgu
    INNER JOIN learning_group_program lgp ON lgp.learning_group_id = lgu.learning_group_id
    INNER JOIN program p ON p.id = lgp.program_id
    INNER JOIN user u ON u.id = lgu.user_id
    WHERE lgp.program_id = 'e4b284da-c94b-4546-84b9-2616e6a32b53'
  ) program_learners_count 
FROM program p
WHERE p.id = 'e4b284da-c94b-4546-84b9-2616e6a32b53';

The above query will count a user multiple times if he is assigned to multiple groups and the program is also assigned to those groups. He should only be counted once.

Update Based on Simonas Petkevičius answer I was able to complete my full query which now looks like this:

SELECT 
    p.*,
    (
        SELECT count(distinct lgl.user_id)
        FROM learning_group_learner lgl
            INNER JOIN learning_group_program lgp 
            ON lgp.learning_group_id = lgl.learning_group_id
        WHERE lgp.program_id = '3772c437-f881-4a69-b846-544d1783b1a4'
    )
FROM program p
WHERE p.id = '3772c437-f881-4a69-b846-544d1783b1a4';

welcome to stackoverflow. it would be best if you could 1) provide relevant table and sample data; 2) describe what you want to achieve. a quick tour on [mcve] would help, really. — Bagus Tesa, Jul 15 '22 at 01:21
Without going much into details of your models, `SELECT count(distinct u.id)` should calculate count without duplicated user ids, however, note possible performance issues: https://stackoverflow.com/questions/11250253/postgresql-countdistinct-very-slow — Simonas Petkevičius, Jul 15 '22 at 06:40
If you could a https://dbfiddle.uk/ with a minimum reproducible example we could help much more efficiently. — Simonas Petkevičius, Jul 15 '22 at 06:44

Simonas Petkevičius · Accepted Answer · 2022-07-15T09:25:23.840

This should do the job:

 SELECT count(distinct lgu.user_id)
   FROM learning_group_user lgu
     INNER JOIN learning_group_program lgp 
       ON lgp.group_id = lgu.group_id

 WHERE lgp.program_id = :programId;

it will only count the distinct userIds, however, note possible performance issues, you might need to optimize it along the way postgresql COUNT(DISTINCT ...) very slow.

Also, note that you don't need two extra joins:

INNER JOIN program p ON p.id = lgp.program_id
INNER JOIN user u ON u.id = lgu.user_id

in your subquery, as you already have user_id and program_id in your many-to-many join tables learning_group_user and learning_group_program.

Sample data and query for your subquery to fiddle around: https://dbfiddle.uk/?rdbms=postgres_14&fiddle=b9a62e9f6d75eb459376a74a589ca46e

Update

Here is a query you could do without a subquery utilizing group by property.

SELECT 
   p.id,
   p.name,
   count(distinct lgu.user_id)
 FROM program as p
   JOIN learning_group_program as lgp ON lgp.program_id = p.id
   JOIN learning_group_user as lgu
        ON lgp.group_id = lgu.group_id

    WHERE lgp.program_id = 99
    group by p.id, p.name;

test: https://dbfiddle.uk/?rdbms=postgres_14&fiddle=2fc33ef133a24165573bb15f6404b17a

This works, I really appreciate your help. I am going to update my question with the full query I am now using which factors in your answer. — brux, Jul 15 '22 at 07:25
Glad to be of help:) However, keep in mind to use Distinct sparingly as it can increase the overhead of the query quite a bit. Read through these answers https://stackoverflow.com/questions/11250253/postgresql-countdistinct-very-slow — Simonas Petkevičius, Jul 15 '22 at 09:14
@brux you could also get rid of the subquery and make your sql more readable at least for me :D Take a look at this dbfiddle: https://dbfiddle.uk/?rdbms=postgres_14&fiddle=2fc33ef133a24165573bb15f6404b17a — Simonas Petkevičius, Jul 15 '22 at 09:25

score 1 · Answer 2 · answered Jul 15 '22 at 07:38

You can change your perspective a bit, like.. using where exists, something like:

select *
from "user" as u
where exists
      (
        select *
        from "learning_group_program" as lp
          inner join "learning_group_user" as lu
          on lp.learning_group_id = lu.learning_group_id
        where lp.program_id = 1
        and lu.user_id = u.id
      );

Fiddle: https://www.db-fiddle.com/f/t7yFyQEc9KYtLKepuQMb6G/0

I used integer as ID, writing full GUID is just too cumbersome.

Removing duplicate entries whilst joining

2 Answers2