CODEWITHSUNDEEP
mysqlsqljoinsum
user725913
user725913

Reputation:

Have SQL calculate the sum for an entire column - Having trouble grouping I believe

The attached picture displays the result set, and I will leave my code below - why am I not able to calculate the sum of the entire viewTotal column within each cell? I want each cell underneath the viewTotal column to read 4.

I would imagine that this is some kind of grouping issue although I can't find anything specific on the web in regards to which columns need to be grouped. It is important that all three rows remain - I don't want to return just one row. Perhaps this criteria makes what I'm trying to do more difficult?

Thank you, Evan

Result Preview

Select topic_id, topic_subject, SUM(topicViews) as viewTotal, replyCount From
                        (
                        Select 
                            T.topic_id, T.topic_subject, Count(distinct Tvt.id) as topicViews, Count(Distinct R.reply_id) as replyCount, R.reply_id, R.reply_topic
                            From topic T                                                                         
                            LEFT JOIN topic_view_tracker Tvt ON
                                T.topic_id = Tvt.topic_id   
                            LEFT Join reply R ON
                                T.topic_id = R.reply_topic
                            Where   
                            T.topic_by = 10
                            Group By T.topic_id) B
                        Group By topic_id
                        Order by replyCount DESC

Sample records:

TOPIC Table

╔══════════╦════════════════════════════╦══════════╗
║ TOPIC_ID ║       TOPIC_SUBJECT        ║ TOPIC_BY ║
╠══════════╬════════════════════════════╬══════════╣
║       25 ║ School police in the night ║       10 ║
║       29 ║ The first topic, enjoy it  ║       10 ║
║       30 ║ This is a normal title...  ║       10 ║
╚══════════╩════════════════════════════╩══════════╝

TOPIC_VIEW_TRACKER Table

╔════╦════════════╦══════════╗
║ ID ║  USER_IP   ║ TOPIC_ID ║
╠════╬════════════╬══════════╣
║  1 ║ xx.xx.xx.x ║       25 ║
║  2 ║ xx.xx.xx.x ║       25 ║
║  3 ║ xx.xxx.xxx ║       29 ║
║  4 ║ xxx.xx.xx  ║       30 ║
╚════╩════════════╩══════════╝

REPLY Table

╔══════════╦═════════════╗
║ REPLY_ID ║ REPLY_TOPIC ║
╠══════════╬═════════════╣
║        1 ║          25 ║
║        2 ║          29 ║
╚══════════╩═════════════╝

Expected output (example):

topic_id       topic subject                          view total   reply count
29         The first topic, enjoy it                    4            5
25          school police in the night                  4            4
30          this is a normal title for a topic ...      4            0

Upvotes: 2

Views: 225

Answers (2)

John Woo
John Woo

Reputation: 263733

SELECT  x.*, 
        COALESCE(y.viewTotal, 0) viewTotal,
        COALESCE(z.replyCount, 0) replyCount
FROM    topic x
        LEFT JOIN
        (
            SELECT  a.topic_by, COUNT(b.topic_ID) viewTotal
            FROM    topic a
                    LEFT JOIN topic_view_tracker b
                        ON a.topic_ID = b.topic_ID
            GROUP   BY a.topic_by
        ) y ON x.topic_by = y.topic_by
        LEFT JOIN
        (
            SELECT  reply_topic, COUNT(*) replyCount
            FROM    reply
            GROUP   BY reply_topic
        ) z ON  x.topic_ID = z.reply_topic
WHERE   x.topic_by = 10

OUTPUT (based on the records provided)

╔══════════╦════════════════════════════╦══════════╦═══════════╦════════════╗
║ TOPIC_ID ║       TOPIC_SUBJECT        ║ TOPIC_BY ║ VIEWTOTAL ║ REPLYCOUNT ║
╠══════════╬════════════════════════════╬══════════╬═══════════╬════════════╣
║       25 ║ School police in the night ║       10 ║         4 ║          1 ║
║       29 ║ The first topic, enjoy it  ║       10 ║         4 ║          1 ║
║       30 ║ This is a normal title...  ║       10 ║         4 ║          0 ║
╚══════════╩════════════════════════════╩══════════╩═══════════╩════════════╝

Upvotes: 1

cha
cha

Reputation: 10411

If you need the total views to be repeated on each row you need to use this:

Select topic_id, topic_subject, (SELECT Count(distinct id) FROM topic_view_tracker WHERE topic_id = B.topic_id)) AS viewTotal, replyCount From
                        (
                        Select 
                            T.topic_id, T.topic_subject, Count(distinct Tvt.id) as topicViews, Count(Distinct R.reply_id) as replyCount, R.reply_id, R.reply_topic
                            From topic T                                                                         
                            LEFT JOIN topic_view_tracker Tvt ON
                                T.topic_id = Tvt.topic_id   
                            LEFT Join reply R ON
                                T.topic_id = R.reply_topic
                            Where   
                            T.topic_by = 10
                            Group By T.topic_id) B
                        Group By topic_id
                        Order by replyCount DESC

But I do not see the reason for this, honestly. You can easily execute SELECT Count(distinct id) FROM topic_view_tracker WHERE topic_id = 10 separately and then execute the rest of your query, omitting topic_view_tracker from it altogether

Upvotes: 0

Related Questions