Reset count when calculating the difference between two rows in SQL

Question

This is the code that I currently have. Which calculates the difference between the first value in comment karma and the next value. Same thing for link karma. I need the count to restart when a new author is displayed. That is the part that I can't figure out.

SELECT [author]
    , [Date (Toronto)]
    , Subreddit
    , Likes
    , Moderator
    , [User: is_gold]
    , [User: link karma]
    , [User: comment karma]
    , COUNT(type) AS #comment
    , coalesce([User: comment karma] - LAG([User: comment karma]) OVER (
            ORDER BY author
                , [Date (Toronto)]
            ), 0) AS diff_comment_karma
    , coalesce([User: link karma] - LAG([User: link karma]) OVER (
            ORDER BY author
                , [Date (Toronto)]
            ), 0) AS diff_link_karma
FROM Comment
GROUP BY Author
    , [Date (Toronto)]
    , Subreddit
    , Likes
    , Moderator
    , [User: is_gold]
    , [User: link karma]
    , [User: comment karma]
    , Type
ORDER BY Author
    , [Date (Toronto)]

This is the result that I am currently getting

author      User: comment karma    User: link karma    diff_comment_karma   diff_link_karma
CheezGrater 20665                   7840               19394                7530
CheezGrater 20665                   7840               0                    0
CheeziTater 1                       1                 -20664               -7839
CheeziTater 1                       1                  0                    0
Cheezsteak7 45                      174                44                   173
CheezulWeezul   1                   1                 -44                  -173
CheezulWeezul   1                   1                  0                    0
chef_baboon 4266                    1643               4265                 1642
chef_baboon 4266                    1643               0                    0
chef_baboon 4266                    1643               0                    0

This is the result that I would like to get

author      User: comment karma    User: link karma    diff_comment_karma   diff_link_karma
CheezGrater 20665                   7840               19394                7530
CheezGrater 20665                   7840               0                    0
CheeziTater 1                       1                  **0                  0**
CheeziTater 1                       1                  0                    0
Cheezsteak  45                      174                **0                    0**
CheezulWeezul   1                   1                  **0                  0**
CheezulWeezul   1                   1                  0                    0
chef_baboon 4266                    1643               **0                  0**
chef_baboon 4266                    1643               0                    0
chef_baboon 4266                    1643               0                    0

Add `PARTITION BY AUTHOR` to your `LAG` and remove AUTHOR from `ORDER BY` — dnoeth, Sep 07 '16 at 20:07
@dnoeth the first row still doesn't make sense to me, shouldn't those diffs be `NULL`? — ebyrob, Sep 07 '16 at 20:10
@ebyrob: Seems there are some additional rows before, due to the `COALESCE` there should be `0`. — dnoeth, Sep 07 '16 at 20:15

Reset count when calculating the difference between two rows in SQL

0 Answers0