Postgres - Count cross more tables

Question

is there a way to do some calculation like this in SQL?

I have 3 tables

Message

ID	Text
1	First question round
2	Second question round

Message Slide

Message audience

I need to know how many slides (questions) should I have answered.

The calculation should be: the sum of (each audience * message slide);

First message has 2 slides/question, so it is: 2 (questions) * 3 (users) = 6

Second message has 1 slide / question, so it is: 1 (question) * 2 (users) = 2

Result I'm looking for is 6 + 2 = 8;

Thank you very much

S-Man · Accepted Answer · 2021-03-01 14:46:59Z

1

Simply join all data sets and count the records?

SELECT
    COUNT(*)
FROM message m
JOIN slide s ON s."MessageID" = m."ID"
JOIN audience a ON a."MessageID" = m."ID"

To count the record number for each message separately you need to group by the message ID, which gives exactly what you are expecting: 6 and 2:

SELECT
    m."ID",
    COUNT(*)
FROM message m
JOIN slide s ON s."MessageID" = m."ID"
JOIN audience a ON a."MessageID" = m."ID"
GROUP BY m."ID"

answered Mar 1, 2021 at 14:30

S-Man

24k9 gold badges51 silver badges78 bronze badges

Sign up to request clarification or add additional context in comments.

1 Answer 1