Clever Geek Handbook

Sql multiplication provided

I am trying to calculate values ​​for new impressions of a column that are equal to the sum (sum_retweet and sum_reply) multiplied by the value in the followers_count column. so for the first line it would be: (2 + 1) * 812, but assuming that the sum of sum_retweet and sum_reply must be greater than zero. If the sum is zero and impressions will be = for followers_count only.

  account_id,    date,        user_screenname, sum_retweet, sum_reply, followers_count, Reach  
    '9',         '2008-06-11',        'A',        '2',         '1',     '812',          '1624'
    '9',         '2008-06-12',        'B',        '0',         '1',     '813',          '813'

Here is my current code:

CREATE VIEW `tweet_sum` AS
    select `tweets`.`account_id` AS `account_id`,
           `tweets`.`user_screenname` AS `user_screenname`,
           CAST(`tweets`.`datetime` as date) AS `period`,
           MAX(`tweets`.`followers_count`) AS `followers_count`,
           SUM(`tweets`.`is_reply`) AS `sum_reply`,
           SUM(`tweets`.`is_retweet`) AS `sum_retweet`,
           MAX(`tweets`.`followers_count`) * ((SUM(`tweets`.`is_reply`) > 0) + (SUM(`tweets`.`is_retweet`) > 0)) as reach
    from `tweets`
    group by cast(`tweets`.`datetime` as date), tweets.username;

HOw am I adding calculations for the impressions column in?

+3


source to share


2 answers


Use a case statement for this:



case when SUM(`tweets`.`is_reply`) + SUM(`tweets`.`is_retweet`) > 0
     then (SUM(`tweets`.`is_reply`) + SUM(`tweets`.`is_retweet`)) 
            * `tweets`.`followers_count`
     else `tweets`.`followers_count` END as newColumn
+2


source


It will be done in SQL, but this syntax may not work in mysql (don't know):

CASE WHEN (Sum_Retweet + Sum_Reply) > 0 
     THEN (Sum_Retweet + Sum_Reply) * followers_count
     ELSE followers_count
END


The field names should be changed to your actual field names.

+1


source






More articles:


All Articles