Postgres SUM over given time

Tags: , , , ,



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!

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


Source: stackoverflow