Postgres SUM over given time

Question

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!

GMB · Accepted Answer · 2020-11-18 17:51:11Z

2

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

answered Nov 18, 2020 at 17:51

GMB

224k25 gold badges103 silver badges151 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Gordon Linoff · Accepted Answer · 2020-11-18 17:55:10Z

0

I think you want generate_series() to generate all days in the month:

SELECT gs.date, sa.id AS salesforce_account_id, acp.campaignid, acp.campaignname,
       SUM(clicks) as clicks
FROM generate_series('2020-10-01'::date, '2020-10-31'::date, interval '1 day') gs(dte) LEFT JOIN
     adwords_campaign_performance acp
     ON acp.date >= gs.dte AND
        acp.date < gs.dte + INTERVAL '1 DAY' LEFT JOIN 
     salesforce_account sa
     ON sa.adwords_id = acp.adwords_customerid
GROUP BY gs.dte, sa.id, acp.campaignid, acp.campaignname, date

answered Nov 18, 2020 at 17:55

Gordon Linoff

1.3m62 gold badges706 silver badges857 bronze badges

Collectives™ on Stack Overflow

Postgres SUM over given time

2 Answers 2

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related