I am trying to SUM google analytics data over a given time using Postgres. I am not quite sure how to get around a grouping issue. The query you see below returns one column per day. I want to SUM all clicks over the given dates. So the query should return 1 row for each campaign with a column on each row indicating the sum of clicks.
SELECT sa.id AS salesforce_account_id, acp.campaignid, acp.campaignname, date, SUM(clicks) as clicks FROM adwords_campaign_performance acp INNER JOIN salesforce_account sa ON sa.adwords_id = acp.adwords_customerid WHERE acp.date >= '2020-10-01' AND acp.date <= '2020-10-03' GROUP BY sa.id, acp.campaignid, acp.campaignname, date
I can write the query so that it returns the number for the entire month as seen below:
SELECT sa.id AS salesforce_account_id, acp.campaignid, acp.campaignname, date_trunc('month', date) AS MONTH, SUM(clicks) as clicks FROM adwords_campaign_performance acp INNER JOIN salesforce_account sa ON sa.adwords_id = acp.adwords_customerid WHERE MONTH = '2020-10-01 00:00:00' AND sa.id = 3148 GROUP BY sa.id, acp.campaignid, acp.campaignname, MONTH
Any help would be greatly appreciated. Thanks!
Advertisement
Answer
The query you see below returns one column per day. I want to SUM all clicks over the given dates. So the query should return 1 row for each campaign with a column on each row indicating the sum of clicks.
So just remove the date
from the select
and group by
clauses:
SELECT sa.id AS salesforce_account_id, acp.campaignid, acp.campaignname, SUM(clicks) as clicks FROM adwords_campaign_performance acp INNER JOIN salesforce_account sa ON sa.adwords_id = acp.adwords_customerid WHERE acp.date >= '2020-10-01' AND acp.date <= '2020-10-03' GROUP BY sa.id, acp.campaignid, acp.campaignname