How to pull data from multiple date ranges with one SQL query?

Question

I have two queries. Each query pulls the total count of orders between organization and customer, and the sum of receivables for the orders. The queries are identical except for the date range.

SELECT org.organization_id, org.name, cust.name as customer,
count(*) as num_orders, round (sum(cast(o.total_charge as real))) as  receivables
FROM 
organization as org, orders as o, organization as cust, reconcile_order as ro
WHERE org.organization_id = o.shipper_org_id
and o.broker_org_id = cust.organization_id
and o.order_id = ro.order_id
and o.status = 'D'
and (ro.receive_payment_in_full = 0 or ro.receive_payment_in_full is NULL)
and (NOW()::DATE - o.delivery_confirmed_date::DATE) < 31
group by org.organization_id, org.name,
cust.name
order by org.name asc limit 20

SELECT org.organization_id, org.name, cust.name as customer,
count(*) as num_orders, round (sum(cast(o.total_charge as real))) as   receivables
FROM 
organization as org, orders as o, organization as cust, reconcile_order as ro
WHERE org.organization_id = o.shipper_org_id
and o.broker_org_id = cust.organization_id
and o.order_id = ro.order_id
and o.status = 'D'
and (ro.receive_payment_in_full = 0 or ro.receive_payment_in_full is NULL)
and (NOW()::DATE - o.delivery_confirmed_date::DATE) between 31 and 60
group by org.organization_id, org.name,
cust.name
order by org.name asc limit 20

But I need to make this one query so that the output is a single table with columns for orders and receivables in the first date range, and next to those columns another pair of columns for the second date range. (i.e. num_orders < 31, receivables < 31, num_orders 31-60, receivables 31-60)

Answer 1

You can put condition statements inside the count() and sum() functions.

So if you adjusted your where clause to bring back all the orders (across both date ranges) then you could make multiple result columns in your select clause, each counting and summing from just the date range you want.

SELECT ...
       count(CASE WHEN (NOW()::DATE - o.delivery_confirmed_date::DATE) < 31 THEN 1 ELSE NULL END) as num_orders_a,
       round(sum(CASE WHEN (NOW()::DATE - o.delivery_confirmed_date::DATE) < 31 THEN cast(o.total_charge as real) ELSE NULL END)) as receivables_a,
       count(CASE WHEN (NOW()::DATE - o.delivery_confirmed_date::DATE) BETWEEN 31 AND 60 THEN 1 ELSE NULL END) as num_orders_b,
       round(sum(CASE WHEN (NOW()::DATE - o.delivery_confirmed_date::DATE) BETWEEN 31 AND 60 THEN cast(o.total_charge as real) ELSE NULL END)) as receivables_b
(same FROM, WHERE, GROUP BY, and ORDER BY sections)