CODEWITHSUNDEEP
postgresqlsubquery
ndemoreau
ndemoreau

Reputation: 3869

PostgreSQL subquery not working

What's wrong with this query?

select extract(week from created_at) as week, 
       count(*) as received, 
       (select count(*) from bugs where extract(week from updated_at) = a.week) as done
from bugs as a
group by week

The error message is:

column a.week does not exist

UPDATE:

following the suggestion of the first comment, I tried this:

select a.extract(week from created_at) as week, 
   count(*) as received, (select count(*) 
from bugs 
where extract(week from updated_at) = a.week) as done from bugs as a group by week

But it doesn't seem to work:

ERROR:  syntax error at or near "from"
LINE 1: select a.extract(week from a.created_at) as week, count(*) a...

Upvotes: 0

Views: 1120

Answers (2)

devanand
devanand

Reputation: 5290

that could work as well

with origin as (
  select extract(week from created_at) as week, count(*) as received
  from bugs
  group by week
)
select week, received, 
  (select count(*) from bugs where week = extract(week from updated_at) )
from origin

it should have a good performance

Upvotes: 1

user330315
user330315

Reputation:

As far as I can tell you don't need the sub-select at all:

select extract(week from created_at) as week, 
       count(*) as received, 
       sum( case when extract(week from updated_at) = extract(week from created_at) then 1 end) as done
from bugs 
group by week

This counts all bugs per week and counts those that are updated in the same week as "done".

Note that your query will only report correct values if you never have more than one year in your table.

If you have more than one year of data in the table you need to include the year in the comparison as well:

select to_char(created_at, 'iyyy-iw') as week, 
       count(*) as received, 
       sum( case when to_char(created_at, 'iyyy-iw') = to_char(updated_at, 'iyyy-iw') then 1 end) as done
from bugs 
group by week

Note that I used IYYY an IW to cater for the ISO definition of the year and the week around the year end/start.

Maybe a little explanation on why your original query did not work would be helpful:

The "outer" query uses two aliases

  • a table alias for bugs named a
  • a column alias for the expression extract(week from created_at) named week

The only place where the column alias week can be used is in the group by clause.

To the sub-select (select count(*) from bugs where extract(week from updated_at) = a.week)) the alias a is visible, but not the alias week (that's how the SQL standard is defined).

To get your subselect working (in terms of column visibility) you would need to reference the full expression of the "outer" column:

(select count(*) from bugs b where extract(week from b.updated_at) = extract(week from a.created_at))

Note that I introduced another table alias b in order to make it clear which column stems from which alias.

But even then you'd have a problem with the grouping as you can't reference an ungrouped column like that.

Upvotes: 3

Related Questions