-2

I have 3 SQLite tables:

channel

channelId triad
1         1
2         1
3         0

video

channelId videoId topic
1         xx      1
1         yy      0
2         pp      0
3         kk      1

comment

commentId replyId videoId sentiment
NULL      er41     xx      -3
clxpo     NULL     kk      0.05
clutz     NULL     yy      2.38
NULL      edg15    xx      1.7
clopq     NULL     pp      1
dkt2      NULL     kk      0.95

For every channel where triad = 1 I need to know the sum of the sentiment from the channel videos where topic = 1 and the number of comments for that channel.

Keys are:

channel.channelId = video.channelId

video.videoId = comment.videoId

End result should be:

channelId sum(sentiment) count(number of comments) triad topic
1         -1,3           2                         1     1

How can I chain that 3 tables to get the needed result?

Vega
  • 2,661
  • 5
  • 24
  • 49
  • I don't get those results. ChannelId 3 has a Triad of 0 so why is it being counted? And how do you get 2.05 from channelid 1 comments? And nothing for 2? – Shawn Nov 28 '19 at 23:47
  • And should the sum and count both be only of videos with topic 1, or is it sum of those with 1, and count of all? – Shawn Nov 28 '19 at 23:51
  • edited the result table, you are right. ChannelId 2 has "triad" but videos for ChannelId 2 don't have "topic" so it gets left out. sum(sentiment) only for those channel and videos with topic=1 and triad=1, same for count. – Vega Nov 29 '19 at 00:26
  • Ok, that makes more sense now - except for a count of 3 instead of 2. – Shawn Nov 29 '19 at 00:52
  • Fixed. Sorry, working for 12 hours now, my brain is dead. – Vega Nov 29 '19 at 00:58

1 Answers1

0

It's a straightforward 3-way merge with some filtering to restrict it to just rows with triad and topic of 1, grouping on channelid:

SELECT ch.channelid
     , sum(co.sentiment) AS sum_sentiment
     , count(*) AS num_comments
FROM channel AS ch
JOIN video AS v ON ch.channelid = v.channelid
JOIN comment AS co ON v.videoid = co.videoid
WHERE ch.triad = 1 AND v.topic = 1
GROUP BY ch.channelid
ORDER BY ch.channelid;

which gives

channelid   sum_sentiment  num_comments
----------  -------------  ------------
1           -1.3           2

If you have a lot of rows in your tables, use the sqlite3 command line tool's .expert command to figure out what indexes should be added for an optimized execution plan.

Shawn
  • 47,241
  • 3
  • 26
  • 60