undefined | Better HN

0 pointsrevicon8y ago0 comments

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.

0 comments

combatentropy8y ago

This 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
    ;

j / k navigate · click thread line to collapse