CODEWITHSUNDEEP
sqlpostgresql
LittleBobbyTables
LittleBobbyTables

Reputation: 4473

Filter column based on other column

I have a table clicks with the following fields:

Its data looks like:

date           page_id     clicks
2015-01-01        1          20
2015-01-02        2          10
2015-01-02        2          5
2015-01-01        4          10
2015-01-02        5          5

There is another table with the page meta information, pages. Some pages are written in English and some in French. The English ones have IDs 1,2,3 and the French ones have ids 2,3,4

I'm trying to create a dataset which splits them into columns like:

date         french_page_clicks    english_page_clicks
2015-01-01         10                   20
2015-01-02         5                    15

The closest question I've found to this is: https://dba.stackexchange.com/questions/45532/create-multiple-columns-from-a-single-column-filtering-criteria where using COUNT(IF(revision = 'pending', 1, NULL)) as pending, they can insert 1 into the table. But I need to insert the actual sum of the clicks.

The furthest I've got (which doesn't work) is:

SELECT
    date,
    SUM (clicks) AS english_page_clicks IF page_id in (1, 2, 3),
    SUM (clicks) AS french_page_clicks IF page_id in (4, 5, 6)
FROM
    clicks
GROUP BY date
ORDER BY date ASC

Or even better, the ability to just reference the pages table into the SUM() like:

SELECT
    date,
    SUM (clicks) AS english_page_clicks IF pages.lang = 'english',
    SUM (clicks) AS french_page_clicks IF pages.lang = 'french'
FROM
    clicks
INNER JOIN pages ON clicks.page_id = pages.id
GROUP BY date
ORDER BY date ASC

How can I filter the columns to create the summary table above?

Upvotes: 2

Views: 46

Answers (1)

jpw
jpw

Reputation: 44881

You need to move the expression into the aggregate function. Try this:

SELECT
    date,
    SUM(case when lang = 'English' then clicks else 0 end) AS english_page_clicks,
    SUM(case when lang = 'French'  then clicks else 0 end) AS french_page_clicks
FROM
    clicks
INNER JOIN pages ON clicks.page_id = pages.id
GROUP BY date
ORDER BY date ASC

Sample SQL Fiddle

Upvotes: 3

Related Questions