SELECT clean_source,
               sumIf(total, clean_demographic = '')                AS total,

               sumIf(female_percent, clean_demographic = '')       AS female_percent,

               sumIf(pos_percent, clean_demographic = 'female')    AS female_pos_percent,
               sumIf(neu_percent, clean_demographic = 'female')    AS female_neu_percent,
               sumIf(neg_percent, clean_demographic = 'female')    AS female_neg_percent,

               sumIf(male_percent, clean_demographic = '')         AS male_percent,
               sumIf(pos_percent, clean_demographic = 'male')      AS male_pos_percent,
               sumIf(neu_percent, clean_demographic = 'male')      AS male_neu_percent,
               sumIf(neg_percent, clean_demographic = 'male')      AS male_neg_percent,

               sumIf(community_percent, clean_demographic = '')    AS community_percent,
               sumIf(pos_percent, clean_demographic = 'community') AS community_pos_percent,
               sumIf(neu_percent, clean_demographic = 'community') AS community_neu_percent,
               sumIf(neg_percent, clean_demographic = 'community') AS community_neg_percent,

               sumIf(post_percent, post_type = '')                 AS post_percent,
               sumIf(pos_percent, post_type = 'socialmedia')       AS post_pos_percent,
               sumIf(neu_percent, post_type = 'socialmedia')       AS post_neu_percent,
               sumIf(neg_percent, post_type = 'socialmedia')       AS post_neg_percent,

               sumIf(repost_percent, post_type = '')               AS repost_percent,
               sumIf(pos_percent, post_type = 'repost')            AS repost_pos_percent,
               sumIf(neu_percent, post_type = 'repost')            AS repost_neu_percent,
               sumIf(neg_percent, post_type = 'repost')            AS repost_neg_percent,

               sumIf(comment_percent, post_type = '')              AS comment_percent,
               sumIf(pos_percent, post_type = 'comment')           AS comment_pos_percent,
               sumIf(neu_percent, post_type = 'comment')           AS comment_neu_percent,
               sumIf(neg_percent, post_type = 'comment')           AS comment_neg_percent,

               sumIf(pos_percent, clean_demographic = '' AND aggregation_type = 0) AS overall_pos_percent,
               sumIf(neu_percent, clean_demographic = '' AND aggregation_type = 0) AS overall_neu_percent,
               sumIf(neg_percent, clean_demographic = '' AND aggregation_type = 0) AS overall_neg_percent
        FROM (
              SELECT CleanSource                                                                      AS clean_source,
                     COUNT(Demographic)                                                               AS total,
                     CleanDemographic                                                                 AS clean_demographic,
                     round(countIf(clean_demographic = 'female') / total * 100)                       AS female_percent,
                     round(countIf(clean_demographic = 'male') / total * 100)                         AS male_percent,
                     round(countIf(clean_demographic = 'community') / total * 100)                    AS community_percent,
                     round(countIf(Sentiment = 'pos') / total * 100)                                  AS pos_percent,
                     round(countIf(Sentiment = 'neg') / total * 100)                                  AS neg_percent,
                     round(countIf(Sentiment = 'neu') / total * 100)                                  AS neu_percent,
                     CAST('', 'Enum8(\\'\\' = 0, \\'comment\\' = 1, \\'repost\\' = 2, \\'socialmedia\\' = 3)') AS post_type,
                     0                                                                                AS post_percent,
                     0                                                                                AS repost_percent,
                     0                                                                                AS comment_percent,
                     0                                                                                AS aggregation_type
              FROM mentions
              WHERE {0}
              GROUP BY ROLLUP(clean_source, clean_demographic)
              UNION ALL
              SELECT CleanSource                                                                 AS clean_source,
                     COUNT()                                                                     AS total,
                     CAST('', 'Enum8(\\'\\' = 0, \\'female\\' = 1, \\'male\\' = 2, \\'community\\' = 3)') AS clean_demographic,
                     0                                                                           AS female_percent,
                     0                                                                           AS male_percent,
                     0                                                                           AS community_percent,
                     round(countIf(Sentiment = 'pos') / total * 100)                             AS pos_percent,
                     round(countIf(Sentiment = 'neg') / total * 100)                             AS neg_percent,
                     round(countIf(Sentiment = 'neu') / total * 100)                             AS neu_percent,
                     PostType                                                                    AS post_type,
                     round(countIf(PostType = 'socialmedia') / total * 100)                      AS post_percent,
                     round(countIf(PostType = 'repost') / total * 100)                           AS repost_percent,
                     round(countIf(PostType = 'comment') / total * 100)                          AS comment_percent,
                     1                                                                           AS aggregation_type
              FROM mentions
              WHERE {0}
              GROUP BY ROLLUP(clean_source, post_type)
                 )
        WHERE clean_source != ''
        GROUP BY clean_source
        ORDER BY total DESC
        LIMIT %(offset)s, %(LIMIT)s;