Select count between dates & All time counts in one query Postgres DB

Question

I want to select count of impression between the dates and All time impression as well, can we do this in one query ?

This is my query in which I am able to get impression only in between dates

SELECT
    robotAds."Ad_ID",
    count(robotScraper."adIDAdID") as ad_impression
FROM
    robot__ads robotAds
LEFT JOIN robot__session__scraper__data robotScraper 
    ON robotScraper."adIDAdID" = robotAds."Ad_ID"
LEFT JOIN robot__session_data robotSession 
    ON robotSession."id" = robotScraper."sessionIDId"
    AND robotSession."Session_start" BETWEEN '2020-11-25 00:00:00'
    AND '2021-04-01 00:00:00'
GROUP BY
    robotAds."Ad_ID"

What I have to do to get count of all time impression in this same query.

Thanks

please always provide a minimal reproducible example and take a look at meta.stackoverflow.com/questions/333952/… — nbk
– nbk, Commented Apr 2, 2021 at 19:01

eshirvana · Accepted Answer · 2021-04-02 23:35:27Z

1

yes you can :

SELECT
    robotAds."Ad_ID",
    count(robotScraper."adIDAdID") filter (where robotSession."Session_start" BETWEEN '2020-11-25 00:00:00'AND '2021-04-01 00:00:00') as ad_impression,
    count(robotScraper."adIDAdID") count_alltime
FROM
    robot__ads robotAds
LEFT JOIN robot__session__scraper__data robotScraper 
    ON robotScraper."adIDAdID" = robotAds."Ad_ID"
LEFT JOIN robot__session_data robotSession 
    ON robotSession."id" = robotScraper."sessionIDId"
     
GROUP BY
    robotAds."Ad_ID"

answered Apr 2, 2021 at 23:35

eshirvana

24.7k3 gold badges28 silver badges43 bronze badges

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

Comments

Paul Maxwell · Accepted Answer · 2021-04-02 23:35:10Z

"Conditional aggregation" should meet this need. Essentially this is using a case expression inside the aggregation function, like this:

SELECT
      robotAds."Ad_ID"
    , count(CASE 
            WHEN robotSession."Session_start" BETWEEN '2020-11-25 00:00:00'
                    AND '2021-04-01 00:00:00'
                THEN 1
            END)                     AS range_ad_impression
    , count(robotScraper."adIDAdID") AS all_ad_impression
FROM robot__ads robotAds
LEFT JOIN robot__session__scraper__data robotScraper ON robotScraper."adIDAdID" = robotAds."Ad_ID"
LEFT JOIN robot__session_data robotSession ON robotSession."id" = robotScraper."sessionIDId"
GROUP BY robotAds."Ad_ID"

Note: the count() function ignores NULLs, above I have ommitted an explicit instruction to return NULL but some prefer to do this using else i.e.

,count(CASE 
        WHEN robotSession."Session_start" BETWEEN '2020-11-25 00:00:00'
                AND '2021-04-01 00:00:00'
            THEN 1 ELSE NULL
        END) AS range_count

Collectives™ on Stack Overflow

Select count between dates & All time counts in one query Postgres DB

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