SQL Query JOIN and Aggregate

user_17857425 0
Viewed 43

New to SQL and have been googling to no avail. Here is the schema:

                   "users"
       Column        |           Type           |          
---------------------+--------------------------+
 id                  | text                     |
 name                | text                     |
 title               | text                     |
 org_id              | text                     |
 type.               | text                     |




                         "organizations"
             Column             |           Type           |  
--------------------------------+--------------------------+
 id                             | text                     |
 name                           | text                     |



                "posts"
      Column       |           Type           |    
-------------------+--------------------------+
 id                | text                     |
 title             | text                     |
 content           | jsonb[]                  |
 owner_id          | text                     |
 org_id            | text                     |
 is_public         | boolean                  |

My goal is to, in one table, show how many private topics, admins, and standard users each organization has, like this:

   Org    | Users | Admins  | Private Posts 
----------+-------+-------+---------------
 Org1     |    56 | 10      |            22
 Org2     |   111 | 10      |            34

Right now, I only get this:

   Org    | Count | Type  | Private Posts 
----------+-------+-------+---------------
 Org1     |    10 | admin |            22
 Org2     |   111 | user  |            34
 Org1     |    56 | user  |            22
 Org2     |    10 | admin |            34

Using:

SELECT t1.id as "Org", t1.cnt as "Count", t1.type as "Type", t2.cnt as "Private Posts" from
    (SELECT COUNT(u.type) as "cnt", u.type as "type", o.id FROM "users" AS u JOIN 
    "organizations" AS o ON o.id=u.org_id GROUP BY u.type, o.id) as t1 join
        (SELECT COUNT(org_id) as "cnt", org_id from posts WHERE is_public = False group 
        by org_id) as t2 on t2.org_id = t1.id;

I basically tried to join the users and organizations and count based on organization and user type (t1), then counted the public posts in posts (t2), and tried to join t1 and t2 based on the organization id. Any help is appreciated.

aggregate-functions count join postgresql sql
1 Answers

Consider a concept called conditional aggregation that pivots results to needed wide formatted columns based on conditional logic. Postgres maintains the useful FILTER for this type of query but can also use CASE statements as shared with other RDBMS's:

SELECT o.id AS "Org",
       COUNT(*) FILTER(WHERE u.type = 'user') AS "Users",
       COUNT(*) FILTER(WHERE u.type = 'admin') AS "Admins",
       COUNT(*) FILTER(WHERE is_public = False) AS "Private Posts"
FROM users u
JOIN organizations o 
   ON o.id = u.org_id
JOIN posts p 
   ON o.id = p.org_id
GROUP BY o.id;
user_14224510
Related