CODEWITHSUNDEEP
postgresqlpostgresql-9.5
Aman Sharma
Aman Sharma

Reputation: 173

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

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

Upvotes: 0

Views: 402

Answers (2)

eshirvana
eshirvana

Reputation: 24593

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"

Upvotes: 1

Paul Maxwell
Paul Maxwell

Reputation: 35593

"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

Upvotes: 0

Related Questions