How to do a COUNTIF formula with mysql

Question

I'm looking at some keyword rankings and need to bring back data on the number of keywords a URL has increased rank for, decreased rank for, stayed the same for. My data looks like this:

URL | Keyword | Position | Previous position | Keyword  Movement

example.com/page1 | things to do london   | 38 | 101| Up
example.com/page2 | mens shoes size 8     | 48 | 94 | Up
example.com/page3 | notebooks             | 22 | 2  | Down
example.com/page4 | macbook pros for sale | 52 | 52 | Same
example.com/page1 | homebrew supplies     | 56 | 46 | Down
example.com/page2 | sql tutorials         | 70 | 39 | Down
example.com/page3 | random seo keywords   | 88 | 36 | Down
example.com/page4 | best albums of 2017   | 94 | 95 | Up
example.com/page5 | fender stratocaster   | 19 | 9  | Down
example.com/page6 | qotsa                 | 91 | 34 | Down

I'd like to have a table showing the URL, number of keyword increases, no. of keyword decreases, and no. of keywords staying the same. In Excel this can be done with a countif formula but not sure how to do this with mysql. I'd like a table looking like the following:

URL |Keywords Up |Keywords Down |Keywords remain


example.com/page1 | 1 | 1 | 0
example.com/page2 | 1 | 1 | 0
example.com/page3 | 0 | 2 | 0
example.com/page4 | 1 | 0 | 1
example.com/page5 | 0 | 1 | 0
example.com/page6 | 0 | 1 | 0

I'm looking for a way of doing a countif on the "Movement" column.

Thanks.

Essentially, what you would like to do is creating a pivot table. The linked duplicate topic describes both static (number of columns is known in advance - this is probably your case) and dynamic (number of columns is not known in advance) pivoting. However, pls note that it may be more efficient to perform such transformation in the application logic than in sql code. — Shadow, Jun 19 '17 at 11:02

score 1 · Accepted Answer · answered Jun 19 '17 at 10:57

You can use condtional aggregation here on the Movement column and tally the three types of movements, grouping by URL.

SELECT
    URL,
    SUM(CASE WHEN Movement = 'Up'   THEN 1 ELSE 0 END) AS keywords_up,
    SUM(CASE WHEN Movement = 'Down' THEN 1 ELSE 0 END) AS keywords_down,
    SUM(CASE WHEN Movement = 'Same' THEN 1 ELSE 0 END) AS keywords_remain
FROM yourTable
GROUP BY URL

How to do a COUNTIF formula with mysql

1 Answers1