CODEWITHSUNDEEP
sqlpostgresqlaggregatedate-arithmeticaggregate-filter
EightB1ts
EightB1ts

Reputation: 23

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?

Upvotes: 2

Views: 1900

Answers (2)

Erwin Brandstetter
Erwin Brandstetter

Reputation: 656666

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:

Upvotes: 2

Clodoaldo Neto
Clodoaldo Neto

Reputation: 125234

This will avoid the costly correlated subquery

select
    pp.id, pp.default_code,
    sum(sol.product_uom_qty) as "Total Sold",
    sum((
        date_trunc('month', pp.create_date) =
        date_trunc('month', current_date) - interval '3 months'
        )::int * sol.product_uom_qty
    ) as "Month 3"
from
    product_product pp
    left join
    sale_order_line sol on pp.id = sol.product_id
group by 1, 2

The cast from boolean to integer results in 0 or 1 which is convenient to be multiplied by the value to be summed

Upvotes: 2

Related Questions