sqlpostgresqlaggregatedate-arithmeticaggregate-filter

Sum of values from 3rd previous month

I'm having difficulty grabbing rows from December (anything from the 3rd previous month). I'm attempting to count the amount of products sold within a certain time period. This is my current query:

 SELECT
   a.id,
   a.default_code,
(
   SELECT SUM(product_uom_qty) 
   AS 
   "Total Sold" 
   FROM 
   sale_order_line c 
   WHERE 
   c.product_id = a.id
),
(
   SELECT SUM(product_uom_qty) 
   AS 
   "Month 3" 
   FROM sale_order_line c 
   WHERE
   c.product_id = a.id
   AND 
   MONTH(c.create_date) = MONTH(CURRENT_DATE - INTERVAL '3 Months')
   AND
   YEAR(c.create_date) = YEAR(CURRENT_DATE - INTERVAL '3 Months')
)
FROM
   product_product a

This is what the DB looks like:

sale_order_line

product_id product_uom_qty  create_date
33         230              2014-07-01 16:47:45.294313

product_product

id  default_code 
33  WHDXEB33

Here's the error I'm receiving:

ERROR:  function month(timestamp without time zone) does not exist
LINE 21:    MONTH(c.create_date) = MONTH(CURRENT_DATE - INTERVAL

Any help pointing me in the right direction?

Solution

  • Use date_trunc() to calculate timestamp bounds:

    SELECT id, default_code
    , (SELECT SUM(product_uom_qty)
        FROM   sale_order_line c 
        WHERE  c.product_id = a.id
       ) AS "Total Sold" 
    , (SELECT SUM(product_uom_qty)
        FROM   sale_order_line c 
        WHERE  c.product_id = a.id
        AND    c.create_date >= date_trunc('month', now()) - interval '2 month'
        AND    c.create_date <  date_trunc('month', now()) - interval '1 month'
      ) AS "Month 3"
FROM   product_product a;

    To get December (now being February), use these expressions:

        AND    c.create_date >= date_trunc('month', now()) - interval '2 month'
    AND    c.create_date <  date_trunc('month', now()) - interval '1 month'

    date_trunc('month', now()) yields '2015-02-01 00:00', after subtracting 2 months, you get '2014-12-01 00:00'. So, "3 months" can be deceiving.

    Also, be sure to use sargable expressions like demonstrated for faster performance and to allow index usage.

    Alternatives

    Depending on your actual DB design and data distribution, this may be faster:

    SELECT a.id, a.default_code, c."Total Sold", c."Month 3"
FROM   product_product a
LEFT  JOIN (
   SELECT product_id AS id
        , SUM(product_uom_qty) AS "Total Sold"
        , SUM(CASE WHEN c.create_date >= date_trunc('month', now()) - interval '2 month'
                   AND  c.create_date <  date_trunc('month', now()) - interval '1 month'
              THEN product_uom_qty ELSE 0 END) AS "Month 3" 
   FROM   sale_order_line
   GROUP  BY 1
   ) c USING (id);

    Since you are selecting all rows, this is probably faster than correlated subqueries. While being at it, aggregate before you join, that's cheaper, yet.
    When selecting a single or few products, this may actually be slower, though! Compare:

    Or with the FILTER clause in Postgres 9.4+:

    ...
        , SUM(product_uom_qty) 
             FILTER (WHERE c.create_date >= date_trunc('month', now()) - interval '2 month'
                     AND   c.create_date <  date_trunc('month', now()) - interval '1 month'
                    ) AS "Month 3"
...

    Details: