2 ms·
makes sense. After removing it... with weeks as ( select week as week from generate_series('2017-01-01'::date, now()::date, '1 week'::interval) week
by revicon 9y ago
makes 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 ;