0

I have Postgres version 9.5. I need to get one query to get a result for date ranges:

2017-02-16 -  2017-02-26
2017-01-16 -  2017-01-26
2016-12-16 -  2016-12-26
2016-11-16 -  2016-11-26

How I can do that with one query? I tried:

SELECT  sum(sell) as sell
FROM "stat" 
LEFT JOIN "stat_key" ON stat.stat_key = stat_key.id 
WHERE ("stat"."date" >= '2017-02-16' AND stat."date" <= '2017-02-26')
AND ("stat"."date" >= '2017-01-16' AND stat."date" <= '2017-01-26')
AND   ("stat"."date" >= '2016-12-16' AND stat."date" <= '2017-12-26')

But it doesn't work.

stat table data

CREATE TABLE stat
AS
  SELECT date::date, sell
  FROM ( VALUES
    ( '2016-11-14', 0 ),
    ( '2017-01-27', 0 ),
    ( '2017-01-27', 0 ),
    ( '2016-11-10', 0 ),
    ( '2016-11-09', 0 ),
    ( '2016-11-09', 0 ),
    ( '2016-12-30', 0 ),
    ( '2016-11-08', 0 ),
    ( '2016-11-08', 7 ),
    ( '2016-11-08', 0 )
  ) AS t(date, sell);

Expected Result

 sell   |     dt     
--------+------------
 131928 | 2017-02-26
 500    | 2017-01-26
 700    | 2016-12-26
 700    | 2016-11-26
  • 1
    Is there always a fixed number of ranges that you need to query for (whether it's three according to your sample code or four according to the list at the beginning)? Are you supplying the ranges as a table or are you building the query dynamically by explicitly encoding the conditions in the WHERE clause? – Andriy M Mar 21 '17 at 14:48
  • In PostgreSQL, by convention we never quote identifiers. – Evan Carroll Mar 21 '17 at 16:07
  • @EvanCarroll: Working with legal, lower-case, unquoted identifiers makes your life easier in Postgres. I would call that good advice, not a "convention". – Erwin Brandstetter Mar 22 '17 at 4:33
  • Those aren't mutually exclusive. – Evan Carroll Mar 22 '17 at 4:55
5

As a minimum, you should use OR instead of AND, because it is impossible for a date to be simultaneously in February AND January.

SELECT sum(sell) AS sell
  FROM stat 
  LEFT JOIN stat_key ON stat.stat_key = stat_key.id 
 WHERE stat.date BETWEEN '2017-02-16' AND '2017-02-26'
    OR stat.date BETWEEN '2017-01-16' AND '2017-01-26'
    OR stat.date BETWEEN '2016-12-16' AND '2017-12-26';

Now that I see your expected output, I know that you want something like this:

WITH ct(d) AS ( 
  SELECT d::date 
  FROM generate_series('2016-11-26', '2017-02-26', INTERVAL '1 month') AS gs(d)
) 
SELECT COALESCE(SUM(s.sell), 0) AS sell, 
       ct.d AS dt  
    FROM  ct 
    LEFT JOIN stat s ON s.date BETWEEN ct.d + '-10 days' AND ct.d 
    GROUP BY ct.d;

Unfortunately, your expected output cannot be generated by the sample input, so I had to test it with my data.

Notes:

  • generate_series works with timestamps: you can convert them to dates in the main SELECT or the CTE. Other approaches are possible.
  • It is possible to avoid the CTE (i.e. the WITH at the beginning) and join directly with SELECT generate_series(.... However, I find it more readable this way, given that in this way the report dates are near the top and that is what you will change most often.
  • LEFT JOIN and COALESCE() let you display results (0) for ranges that have no values at all in the data table.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.