postgresql - sql query select two periods for revenue

Question

how is it possible to compare two period using one query for example:

SELECT s_campaign_id, COALESCE(SUM(f_revenue),0) AS revenue FROM tbl_reports WHERE (d_gen_date>='2020-12-15' AND d_gen_date<='2020-12-16') GROUP BY s_campaign_id

and have compare this with revenue of other period in the same query so for example i will want to get a list of revenue of period 1 + period 2

SELECT s_campaign_id, COALESCE(SUM(f_revenue),0) AS revenue FROM tbl_reports WHERE (d_gen_date>='2020-12-15' AND d_gen_date<='2020-12-16') as revenue1  AND (d_gen_date>='2019-12-13' AND d_gen_date<='2020-12-14') as revenue2 GROUP BY s_campaign_id

thanks

Answer 1

You could use FILTER:

SELECT
    s_campaign_id
    , COALESCE ( SUM ( f_revenue ) FILTER(WHERE d_gen_date >= '2020-12-15' AND d_gen_date <= '2020-12-16'), 0 ) AS revenue1 
    , COALESCE ( SUM ( f_revenue ) FILTER(WHERE d_gen_date >= '2019-12-13' AND d_gen_date <= '2020-12-14'), 0 ) AS revenue2 
FROM
    tbl_reports 
WHERE
    ( d_gen_date >= '2020-12-15' AND d_gen_date <= '2020-12-16' )
OR  
    ( d_gen_date >= '2019-12-13' AND d_gen_date <= '2020-12-14' ) 
GROUP BY
    s_campaign_id;