Trying to count the number of occurences that 3 columns from 2 tables have on my organizations table-CodePudding

-- 2. In one table, show how many private topics, admins, and standard users each organization has.


SELECT organizations.name, COUNT(topics.privacy) AS private_topic, COUNT(users.type) AS user_admin, COUNT(users.type) AS user_standard
FROM  organizations
 LEFT JOIN topics 
  ON organizations.id=topics.org_id
  AND topics.privacy='private'
 LEFT JOIN users
  ON users.org_id=organizations.id
  AND users.type='admin'
 LEFT JOIN users
  ON users.org_id=organizations.id
  AND users.type='standard'
GROUP BY organizations.name
;

org_id is the foreign key that reals both the users table and topics table. It keeps giving me the wrong result by only either counting the number of admins or standard users and putting that for all rows in the each column. Any help is really appreciated as I have been stuck on this for a while now!

So, I am getting an error when I do as you said which is that the users table cannot be specified more than once. I updated the code to how you said to write it but still nothing. They really don't give me any sample data either but I just made some queries and saw the number of times there are private topics for example, which is in the privacy column of the topics table. When I dont get this error as I said, the joins seem to overwrite themselves where each row for all the columns is the same as the last join.

CodePudding user response：

It appears to me that topics and users have no relationship. You're just trying to get the result together in a single query. There are other and possibly better ways to accomplish that but I think this will fix what you've got already (assuming you have id columns for each table.)

SELECT 
  organizations.name,
  COUNT(DISTINCT topics.id) AS private_topic,
  COUNT(DISTINCT users.id) FILTER (WHERE users.type = 'admin')    AS user_admin, 
  COUNT(DISTINCT users.id) FILTER (WHERE users.type = 'standard') AS user_standard`
FROM organizations
  LEFT JOIN topics 
    ON organizations.id = topics.org_id AND topics.privacy = 'private'
  LEFT JOIN users
    ON users.org_id = organizations.id
GROUP BY organizations.name;

I propose this as a more straightforward way:

SELECT 
  min(o.name) as "name",
  (
    select count(*) from topics t
    where t.org_id = o.id AND t.privacy = 'private'
  ) as private_topics,
  (
    select count(*) from users u
    where u.org_id = o.id and u.type = 'admin'
  ) AS user_admin,
  (
    select count(*) from users u
    where u.org_id = o.id and u.type = 'standard'
  ) AS user_standard
FROM organizations o
GROUP BY o.id;