PostgreSQL subquery not working

Question

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...

You cannot reference an alias like that you need to use a.extract(week from created_at) instead — user330315
– user330315, Commented Dec 19, 2013 at 7:54
This makes sense but it doesn't seem to work. I could have misunderstood... — ndemoreau
– ndemoreau, Commented Dec 19, 2013 at 8:08
Ok, I removed it, and the a.created_at as well but I still get the same error message. In the error message, the cursor is place in front of the first from (week from created_at). Without the a in front of extract, it works. — ndemoreau
– ndemoreau, Commented Dec 19, 2013 at 8:26
a is an table alias, you cannot call a function on it (a.extract) — user330315
– user330315, Commented Dec 19, 2013 at 8:37

score 3 · Accepted Answer · 2013-12-19 08:53:04Z

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.

devanand · Accepted Answer · 2013-12-19 09:22:18Z

1

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

answered Dec 19, 2013 at 9:22

devanand

5,4192 gold badges22 silver badges19 bronze badges

1 Comment

devanand Over a year ago

it's not the "with origin" its just "with 'name'". i used origin as name for the following query. replace origin with any other name you like

Collectives™ on Stack Overflow

PostgreSQL subquery not working

2 Answers 2

Comments

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related