CODEWITHSUNDEEP
sqlpostgresqldatetimesumaggregate-functions
00robinette
00robinette

Reputation: 557

Postgres SUM over given time

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!

Upvotes: 0

Views: 299

Answers (2)

Gordon Linoff
Gordon Linoff

Reputation: 1269633

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

Upvotes: 0

GMB
GMB

Reputation: 222432

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

Upvotes: 2

Related Questions