SETUP multiple sums with multiple date ranges

Question

Here is the query I am trying to complete.

SELECT salesrep, 
    SUM(lines_total) AS OTSUM,
    SUM(order_type = 'NEW') OTNEW,
    SUM(order_type = 'REPEAT') OTREPEAT,
    SUM(lines_total 
      WHERE order_closed BETWEEN '2020-11-01' AND '2020-11-30') AS OTLMSUM
    SUM(lines_total 
      WHERE order_closed BETWEEN '2020-12-01' AND '2020-12-31') AS OTTMSUM
FROM `ranger_orders`
WHERE order_closed BETWEEN '2020-01-01' AND '2020-12-31'

Of course this fails, but how do I setup the SUMS for multiple date ranges, the WHERE date range at the end needs to stay as is.

For these lines:

SUM(lines_total WHERE order_closed BETWEEN '2020-11-01' AND '2020-11-30') AS OTLMSUM
SUM(lines_total WHERE order_closed BETWEEN '2020-12-01' AND '2020-12-31') AS OTTMSUM

Answer 1

You need CASE expressions for conditional aggregation, like:

SUM(CASE WHEN order_closed BETWEEN '2020-12-01' AND '2020-12-31' THEN lines_total ELSE 0 END) AS OTTMSUM

For each of the sums, change BETWEEN '2020-12-01' AND '2020-12-31' accordingly.