Query with different time period in same query ( WHERE ) I compare the some total numbers between today and 1 months ago.

I created a query like this. Below you will see "commented" Where statements since I cannot get both results in same query

SELECT
  COUNT(DISTINCT a.order_id) AS total_orders,
  SUM(invoice_amount)        AS total_amount
FROM
  order_items a
  INNER JOIN orders b ON a.order_id = b.order_id
--WHERE CURRENT_DATE &gt;= b.order_creation_date; 
--WHERE CURRENT_DATE - INTERVAL '1 month' &gt; b.order_creation_date 
AND b.seller_id IN(1,3)


I wrote this query that would solve my problem but I think it is too much complex. I wish there was a better one.

SELECT
  COUNT(DISTINCT a.order_id)
    FILTER (WHERE CURRENT_DATE &gt;= b.order_creation_date AND b.seller_id IN (1, 3))                     AS total_orders_until_now,
  SUM(invoice_amount)
    FILTER (WHERE CURRENT_DATE &gt;= b.order_creation_date AND b.seller_id IN (1, 3))                     AS total_amount_until_now,
  COUNT(DISTINCT a.order_id)
    FILTER (WHERE CURRENT_DATE - INTERVAL '1 month' &gt; b.order_creation_date AND b.seller_id IN (1, 3)) AS total_orders_until_a_month_ago,
  SUM(invoice_amount)
    FILTER (WHERE CURRENT_DATE - INTERVAL '1 month' &gt; b.order_creation_date AND b.seller_id IN (1, 3)) AS total_amount_until_a_month_ago
FROM
  order_items a
  INNER JOIN orders b ON a.order_id = b.order_id
Well first factor our the seller_id IN (1,3) and use USING

SELECT
  COUNT(DISTINCT order_id)
    FILTER (WHERE CURRENT_DATE &gt;= b.order_creation_date)                     AS total_orders_until_now,
  SUM(invoice_amount)
    FILTER (WHERE CURRENT_DATE &gt;= b.order_creation_date)                     AS total_amount_until_now,
  COUNT(DISTINCT order_id)
    FILTER (WHERE CURRENT_DATE - INTERVAL '1 month' &gt; b.order_creation_date) AS total_orders_until_a_month_ago,
  SUM(invoice_amount)
    FILTER (WHERE CURRENT_DATE - INTERVAL '1 month' &gt; b.order_creation_date) AS total_amount_until_a_month_ago
FROM order_items a
INNER JOIN orders b
  USING (order_id)
WHERE b.seller_id IN (1, 3)


You can use GROUPING to further clean this up (I guess) if you can accept a different format..

SELECT
  CASE
    WHEN CURRENT_DATE - INTERVAL '1 month' &gt; b.order_creation_date THEN 'until_a_month_ago'
    WHEN CURRENT_DATE &gt;= b.order_creation_date THEN 'until_now'
  END,
  COUNT(DISTINCT order_id) AS total_orders,
  SUM(invoice_amount) AS total_amount
FROM order_items a
INNER JOIN orders b
  USING (order_id)
WHERE b.seller_id IN (1, 3)
GROUP BY ( CASE
  WHEN CURRENT_DATE - INTERVAL '1 month' &gt; b.order_creation_date THEN 'until_a_month_ago'
  WHEN CURRENT_DATE &gt;= b.order_creation_date THEN 'until_now'
END )