CODEWITHSUNDEEP
sqlpostgresql
Tarlen
Tarlen

Reputation: 3797

Counting associations from multiple tables

I want to see how many association each of my records in a given table have. Some of these association have some conditions attached to them

So far I have

-- Count app associations
SELECT 
distinct a.name, 
COALESCE(v.count, 0) as visitors, 
COALESCE(am.count, 0) AS auto_messages,
COALESCE(c.count, 0) AS conversations
FROM apps a
LEFT JOIN (SELECT app_id, count(*) AS count FROM visitors GROUP BY 1) v ON a.id = v.app_id
LEFT JOIN (SELECT app_id, count(*) AS count FROM auto_messages GROUP BY 1) am ON a.id = am.app_id
LEFT JOIN (
    SELECT DISTINCT c.id, app_id, count(c) AS count 
    FROM conversations c LEFT JOIN messages m ON m.conversation_id = c.id
    WHERE m.visitor_id IS NOT NULL
    GROUP BY c.id) c ON a.id = c.app_id
WHERE a.test = false
ORDER BY visitors DESC;

I run into problem with the last join statement for conversations. I want to count the number of conversations that have at least 1 message where the visitor_id is not null. For some reason, I get multiple records for each app, ie. the conversations are not being grouped properly.

Any ideas?

Upvotes: 0

Views: 41

Answers (1)

Kevin Anderson
Kevin Anderson

Reputation: 4592

My gut feeling, based on limited understanding of the big picture: in the nested query selecting from conversations,

  • remove DISTINCT
  • remove c.id from SELECT list
  • GROUP BY c.app_id instead of c.id

EDIT: try this 

...
LEFT JOIN (
    SELECT app_id, count(*) AS count 
    FROM conversations c1
    WHERE 
    EXISTS (
        SELECT *
        FROM messages m
        WHERE m.conversation_id = c1.id and
            M.visitor_id IS NOT NULL
    )
    GROUP BY c1.app_id) c
ON a.id = c.app_id

Upvotes: 1

Related Questions