CODEWITHSUNDEEP
sqlpostgresql
Jack
Jack

Reputation: 65

Postgres - Count cross more tables

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

ID MessageID Question
1 1 How do you feel?
2 1 Where do you work?
3 2 Skiing or swimming?

Message audience

ID MessageID UserID
1 1 1
2 1 2
3 1 3
4 2 1
5 2 2

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

Upvotes: 1

Views: 31

Answers (1)

S-Man
S-Man

Reputation: 23716

demo:db<>fiddle

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"

Upvotes: 1

Related Questions