4 ms·
Weird, the example in the post (after changing table/field names for my database) with weeks as ( select week as week from generate_series('2017-01-0
by revicon 9y ago
Weird, the example in the post (after changing table/field names for my database)
with weeks as (
select week as week
from generate_series('2017-01-01'::date, now()::date, '1 week'::interval) weeks
),
SELECT weeks.week,
count(*)
FROM weeks,
test_results
WHERE
test_results.date_created > weeks.week
AND
test_results.date_created <= weeks.week - '1 week'::interval
Throws an error for me...
ERROR: syntax error at or near "SELECT"
LINE 5: SELECT weeks.week,
^
- cmaggard 9y agoRemove the comma before the SELECT?
- barsonme 9y agoyeah, the comma after the "with" block shouldn't be there. i.e., ... weeks ) SELECT weeks.week
- revicon 9y agomakes sense. After removing it... with weeks as ( select week as week from generate_series('2017-01-01'::date, now()::date, '1 week'::interval) weeks ) SELECT weeks.week, count(*) FROM weeks, test_results WHERE test_results.date_created > weeks.week AND test_results.date_created <= weeks.week - '1 week'::interval it throws... ERROR: column "week" does not exist LINE 2: select week as week ^ I would move this to the post's own "replies" section, but it doesn't have one.
- combatentropy 9y agoThis should work. I'm sorry, I conformed it to my own style (lowercase and different indentation). with weeks as ( select week from generate_series( '2017-01-01'::date, now()::date, '1 week'::interval ) week ) select weeks.week, count(1) from weeks, test_results where test_results.date_created > weeks.week and test_results.date_created <= (weeks.week - '1 week'::interval) group by week ;